Tech Mates

A look at the salient features of Jan Lokpal Bill ...

2011-04-14T10:47:00.001+05:30

1. An institution called LOKPAL at the centre and LOKAYUKTA in each state will be set up

2. Like Supreme Court and Election Commission, they will be completely independent of the governments. No minister or bureaucrat will be able to influence their investigations.

3. Cases against corrupt people will not linger on for years anymore: Investigations in any case will have to be completed in one year. Trial should be completed in next one year so that the corrupt politician, officer or judge is sent to jail within two years.

4. The loss that a corrupt person caused to the government will be recovered at the time of conviction.

5. How will it help a common citizen: If any work of any citizen is not done in prescribed time in any government office, Lokpal will impose financial penalty on guilty officers, which will be given as compensation to the complainant.

6. So, you could approach Lokpal if your ration card or passport or voter card is not being made or if police is not registering your case or any other work is not being done in prescribed time. Lokpal will have to get it done in a month's time. You could also report any case of corruption to Lokpal like ration being siphoned off, poor quality roads been constructed or panchayat funds being siphoned off. Lokpal will have to complete its investigations in a year, trial will be over in next one year and the guilty will go to jail within two years.

7. But won't the government appoint corrupt and weak people as Lokpal members? That won't be possible because its members will be selected by judges, citizens and constitutional authorities and not by politicians, through a completely transparent and participatory process.

8. What if some officer in Lokpal becomes corrupt? The entire functioning of Lokpal/ Lokayukta will be completely transparent. Any complaint against any officer of Lokpal shall be investigated and the officer dismissed within two months.

9. What will happen to existing anti-corruption agencies? CVC, departmental vigilance and anti-corruption branch of CBI will be merged into Lokpal. Lokpal will have complete powers and machinery to independently investigate and prosecute any officer, judge or politician.

10. It will be the duty of the Lokpal to provide protection to those who are being victimized for raising their voice against corruption.

What is Jan Lokpal Bill ?

2011-04-14T10:40:00.002+05:30

The Jan Lokpal Bill (Citizen's ombudsman Bill) is a draft anti-corruption bill drawn up by prominent civil society activists seeking the appointment of a Jan Lokpal, an independent body that would investigate corruption cases, complete the investigation within a year and envisages trial in the case getting over in the next one year.

Drafted by Justice Santosh Hegde (former Supreme Court Judge and present Lokayukta of Karnataka), Prashant Bhushan (Supreme Court Lawyer) and Arvind Kejriwal (RTI activist), the draft Bill envisages a system where a corrupt person found guilty would go to jail within two years of the complaint being made and his ill-gotten wealth being confiscated. It also seeks power to the Jan Lokpal to prosecute politicians and bureaucrats without government permission.

Retired IPS officer Kiran Bedi and other known people like Swami Agnivesh, Sri Sri Ravi Shankar, Anna Hazare and Mallika Sarabhai are also part of the movement, called India Against Corruption. Its website describes the movement as "an expression of collective anger of people of India against corruption. We have all come together to force/request/persuade/pressurize the Government to enact the Jan Lokpal Bill. We feel that if this Bill were enacted it would create an effective deterrence against corruption."

Anna Hazare, anti-corruption crusader, began a fast-unto-death today, demanding that this bill, drafted by the civil society, be adopted. The website of the India Against Corruption movement calls the Lokpal Bill of the government an "eyewash" and has on it a critique of that government Bill. It also lists the difference between the Bills drafted by the government and civil society.

Who is Anna Hazare ?

2011-04-14T10:34:00.002+05:30

Anna Hazare , An ex-army man. Fought 1965 Indo-Pak War.

He built a village Ralegaon Siddhi in Ahamad Nagar district, Maharashtra. This village is a self-sustained model village. Energy is produced in the village itself from solar power, biofuel and wind mills. In 1975, it used to be a poverty clad village. Now it is one of the richest village in India. It has become a model for self-sustained, eco-friendly & harmonic village.

This guy, Anna Hazare was awarded Padma Bhushan and is a known figure for his social activities.

He is supporting a cause, the amendment of a law to curb corruption in India.

He is advocating for a Bil, The Lok Pal Bill (The Citizen Ombudsman Bill), that will form an autonomous authority who will make politicians (ministers), bureaucrats (IAS/IPS) accountable for their deeds.

What is Moonlight ?

2010-03-14T18:10:00.002+05:30

Moonlight is an open source implementation of Silverlight, primarily for Linux and other Unix/X11 based operating systems. In September of 2007, Microsoft and Novell announced a technical collaboration that includes access to Microsoft's test suites for Silverlight and the distribution of a Media Pack for Linux users that will contain licensed media codecs for video and audio.

Moonlight 2 was released December 17, 2009

How to find the version of SQL Server using ?

2009-12-11T09:00:00.003+05:30

For SQL Server 2005/2008

SELECT SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')

For SQL Server 2000

select @@version

Microsoft 70-528 Paper

2009-09-11T14:45:00.003+05:30

Q.1. Your Web site uses custom Themes. Your Web site must support additional Themes based on the user's company name. The company name is set when a user logs on to the Web site. The company's Theme name is stored in a variable named ThemeName. You need to use this variable to dynamically set the Web site's Theme. What should you do?

A. Add the following code segment to the markup source of each page on the Web site.
<%@ Page Theme="ThemeName" ... %>

B. Add the following code segment to the Load event of each page on the Web site.
Page.Theme = ThemeName;

C. Add the following code segment to the PreInit event of each page on the Web site.
Page.Theme = ThemeName;

D. Add the following code segment to the Web site's configuration file.

Answer: C

Q.2. Your Web site uses custom Themes. Your Web site must support additional Themes based on the user's company name. The company name is set when a user logs on to the Web site. The company's Theme name is stored in a variable named ThemeName. You need to use this variable to dynamically set the Web site's Theme. What should you do?

A. Add the following code segment to the markup source of each page on the Web site.
<%@ Page Theme="ThemeName" ... %>

B. Add the following code segment to the Load event of each page on the Web site.
Page.Theme = ThemeName

C. Add the following code segment to the PreInit event of each page on the Web site.
Page.Theme = ThemeName

D. Add the following code segment to the Web site's configuration file.

Answer: C

Why Https ?

2009-09-08T12:42:00.002+05:30

if we visit a website or webpage, and look at the address in the web browser, it will likely begin with the following: http://. This means that the website is talking to our browser using the regular 'unsecure' language. In other words, it is possible for someone to "eavesdrop" on our computer's conversation with the website. If we fill out a form on the website, someone might see the information we send to that site.This is why we never ever enter our credit card number in an http website.But if the web address begins with https://, that basically means our computer is talking to the website in a secure code that no one can eavesdrop on. So If a website ever asks us to enter our credit card information, we should automatically look to see if the web address begins with https://. Although it may be encrypted does not mean its safe, there are tools out there to decrypt the information being sent over the wire, although its more difficult to do so.

How Https Works ?

2009-09-08T12:40:00.001+05:30

To prepare a web-server for accepting https connections the administrator must create a public key certificate for the web-server.This certificate must be signed by a certificate authority of one form or another, who certifies that the certificate holder is who they say they are. Web browsers are generally distributed with the signing certificates of major certificate authorities, so that they can verify certificates signed by them.

What is Https ?

2009-09-08T12:34:00.000+05:30

HTTPs is hypertext transfer protocol over Secure Socket Layer , which is responsible for sending and receiving confidential information with a server.So the information must be secured in order to prevent unauthorized access.HTTPS uses port 443 instead of HTTP port 80 in its interactions with the lower layer, TCP/IP.It is a Web protocol developed by Netscape and built into its browser that encrypts and decrypts user page requests as well as the pages that are returned by the Web server. HTTPS is really just the use of Netscapes Secure Socket Layer (SSL) as a sub layer under its regular HTTP application layering. This ensures reasonable protection from eavesdroppers. HTTPS is normally used in login pages, shopping/commercial sites etc.

What is Http ?

2009-09-08T12:07:00.000+05:30

HTTP is hyper text transfer protocol which is responsible for sending and receiving information across the Internet.It is transmitted over the web via PORT 80(TCP).We normally use HTTP while browsing the web, but its not secure and so someone can eavesdrop the conversation between the web server and us.javascript:void(0)

YSR Pada Yatra

2009-09-03T13:52:00.002+05:30

YSR burst onto the national stage with his padyatra during the campaign for the 2004 assembly elections. Many saw him as the centerpiece of the Congress strategy against the all-powerful TDP.

YSR expanded his political empire from Andhra Pradesh's feudal Reddy heartland. He won 4 Lok Sabha and 4 Assembly elections from Kadappa. He was credited publicly and often with being a leader with a huge mass base, a rarity in the Congress party.

But his last term in office was marred by accusations of corruptions and scams. His son's media empire expanded rapidly under his father's watch. Critics of the YSR regime, like the powerful Eenadu media group, were severely dealt with. Even the Satyam scandal, it was alleged, had an YSR connection.

And when superstar Chiranjeevi decided to throw his weight into the last elections, many doubted what YSR would accomplish for the Congress. But in his campaign, YSR claimed he was a development man. He fought on the back of populist schemes: irrigation, pensions, schemes for women

The YSR magic worked, and his party's victory was seen as hard evidence of a grassroots politician whose charisma trounced the controversy surrounding him.

YSR killed in chopper crash: Express your condolences

2009-09-03T13:49:00.000+05:30

I deeply regret the sudden demise of my beloved chief Minister Shri Y.S.Rajasekhar Reddy. My heart felt condolence to the departed great leader.

Who was there with YSR on his chopper

2009-09-03T13:40:00.000+05:30

The bodies of Andhra Pradesh Chief Minister YSR Reddy, his Special Secretary P Subramanyam, Chief Secretary A S C Wesley, pilot Group Captain S K Bhatia and co-pilot M S Reddy were found on Rudrakonda Hill, 40 nautical miles east of Kurnool.

Subramaniam was a 1983 batch officer and had been working at the Chief Minister's office for nearly five years. Chief Security Officer ASC Wesley had joined the Chief Minister's office just two moths ago.

The two pilots on board the seven-seater Bell 430 helicopter had several hours of flying.

According to reports, their bodies will be first flown to kurnool and then to Hyderabad.

YSR & 4 others killed in chopper crash

2009-09-03T13:27:00.004+05:30

Andhra Pradesh Chief Minister Y S Rajasekhara Reddy and four others including 2 Pilots Capt S K Bhatia and Capt M S Reddy, chief security officer A S C Wesley and his special secretary P Subramaniam were killed when his helicopter crash-landed atop the Rudrakonda hill in the Nallamalla range, about 40 nautical miles east of Kurnool.

Indian Air Force personnel located the helicopter in the deep forests on Thursday morning, after 24 hours of massive land, air and satellite-backed search.

The 11-year-old Bell 430 helicopter with five on board had lost radio contact one hour after it took off from the old Begumpet Airport in Hyderabad on Wednesday 02 Sept 2009.

Highly placed Congress sources said the party was not in a hurry to convene the meeting of Congress Legislature Party to elect a leader and the same could take place after three or four days.

Sare jaha se accha Hindustaan Hamara Hamaaraaaa

2009-08-15T11:53:00.007+05:30

"Long years ago we made a tryst with destiny, and now the time comes when we will redeem our pledge, not wholly or in full measure, but very substantially. At the stroke of the midnight hour, when the world sleeps, India will awake to life and freedom. A moment comes, which comes but rarely in history, when we step out from the old to the new, when an age ends and when the soul of a nation, long suppressed, finds utterance.... We end today a period of ill fortune, and India discovers herself again."

- Jawaharlal Nehru
(Speech on Indian Independence Day, 1947)

On 15 August 1947, India attained freedom from the British Rule. Every year, August 15 is celebrated as the Independence Day in India. This national festival is celebrated with great enthusiasm all over the country.

The Independence Day of any country is a moment of pride and glory. On this special occasion, rich tributes are paid to the freedom fighters who sacrificed their lives and fought to free their motherland from the clutches of the oppressors - British who ruled the country.

The British, had initially come for trade but gradually took over the total administration of the country.

At the strike of midnight of the August 15, 1947, India shook off the shackles of British Rule and became free. It was a night of celebration all over the country.

Commemorating the day India attained freedom (15th August); Independence Day is celebrated with flag hoisting ceremonies and cultural programmes all over the country. The main program is held at the Red Fort where the Prime Minster unfurls the National Flag and it is saluted by Guns.

The Prime Minister's speech at the Red Fort in Delhi is a major highlight. Patriotic presentations by school children add colors to the celebrations.

Delhi’s skyline gets dotted with thousands of kites taking to the sky this day. Similar programmes are held at all the State capitals also.

The preparations begin a month in advance. Roads are decorated with flags and buntings. Buildings of national importance are illuminated.

HAPPY INDEPENDENCE DAY

What new data source types were added in SSRS 2005?

2009-08-10T14:17:00.001+05:30

In addition to the data source types available in SSRS 2000 (SQL Server, Oracle, ODBC, OLE DB), the following have been added in SSRS 2005:

* SQL Server 2005 Analysis Services
* SQL Server 2005 Integration Services
* SQL Server 2005 Report Builder Models
* XML (through URL and Web services)
* SAP (available through download)

Do I need Visual Studio 2005 to design reports?

2009-08-10T14:16:00.000+05:30

No. Unlike SSRS 2000, which required users to purchase Visual Studio 2003, SQL Server 2005 comes with Business Intelligence Development Studio, which provides the Visual Studio shell and the business intelligence projects including Report Designer and Report Builder Model Designer. If users already have Visual Studio 2005, these projects are installed along with language projects.

How do users use Report Builder with SQL Server data sources?

2009-08-10T14:15:00.000+05:30

While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio–based development shell.

How does Report Builder support Analysis Services cubes?

2009-08-10T14:13:00.002+05:30

Report Builder supports relational SQL and Analysis Services data sources in SQL Server 2005. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

What is Report Builder?

2009-08-10T14:13:00.001+05:30

Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.

What tools can be used to manage SSRS 2005?

2009-08-10T14:11:00.001+05:30

Report Manager, the Web-based management tool, is still available in SSRS 2005. In additions, all of the Report Manager functionality is now available in SQL Server 2000 Management Studio, a Windows-based console that gives you a single place to manage all components of SQL Server. Finally, a new Reporting Services Configuration tool allows you to manage various aspects of your report server, including service identities and database connections.

How to upgrade from SQL Server 2000 Reporting Services to SQL Server 2005 Reporting Services?

2009-08-10T14:09:00.000+05:30

You can upgrade most installations using the SQL Server 2005 setup program. All report server content and settings will be fully functional after the upgrade process.

In cases where you have customized your report server deployment (added extensions, changed virtual directory settings, or encrypted the ASP.NET account user name in the registry), you will need to migrate your Reporting Services installation. In this case, you install a new instance of SSRS 2005 and use the Reporting Services configuration tool to migrate settings.

If you deployed custom extensions for Reporting Services 2000, you may need to rebuild these extensions so that SSRS 2005 can use them. Consult SQL Server Books Online for specific instructions on what you need to do to move to SSRS 2005.

What’s new in SQL Server 2005 Reporting Services?

2009-08-10T14:07:00.001+05:30

SQL Server 2005 retains the core functionality of SQL Server 2000 Reporting Services. The product contains several key additions and enhancements, including the following:

* Report Builder (end-user reporting)
* Report Controls (ships with Microsoft Visual Studio 2005)
* Direct client printing (first shipped in SQL Server 2000 Service Pack 2)
* SharePoint Reporting Web Parts (first shipped in SQL Server 2000 Service Pack 2)
* End-user sort
* Multivalued parameters
* Custom report items
* Report Designer improvements
* Analysis Services Query Designer
* Management Studio integration

What is SQL Server 2005 Reporting Services?

2009-08-10T14:03:00.002+05:30

SQL Server 2005 Reporting Services is a server-based enterprise reporting environment, managed through Web services, that delivers a variety of interactive and printed reports.

SAP IS Retail Online Training

2009-07-28T12:29:00.018+05:30

Dear Aspirant,

Greetings from SAP Trainers.

Exceptional SAP courses (SD, CRM & IS – Retail) available, fully tailored to meet market requirements with the added bonus of free SAP Server Access for 2 months.

You will be trained with a certified Consultant with industry experience.

SAP IS Retail 30Hr. Duration and course fee is $200 only

For more Details
Email : 4saptrainer@gmail.com
Skype ID : saptrainers

SAP CRM Online Training

2009-07-27T15:28:00.003+05:30

Dear Aspirant,

Greetings from SAP Trainers.

Exceptional SAP courses (SD, CRM & IS – Retail) available, fully tailored to meet market requirements with the added bonus of free SAP Server Access for 2 months.

You will be trained with a certified Consultant with industry experience.

This course not will only deliver the SAP module training,but also the understanding of ASAP methodology.

SAP CRM 30Hr. Duration and course fee is $150 only

For more Details
Email : 4saptrainer@gmail.com
Skype ID : saptrainers

SAP SD Online Training

2009-07-26T11:07:00.003+05:30

Dear Aspirant,

Greetings from SAP Trainers.

Exceptional SAP courses (SD, CRM & IS – Retail) available, fully tailored to meet market requirements with the added bonus of free SAP Server Access for 2 months.

You will be trained with a certified Consultant with industry experience.

This course not will only deliver the SAP module training,but also the understanding of ASAP methodology.

SAP SD 30Hr. Duration and course fee is $150 only

For more Details
Email : 4saptrainer@gmail.com
Skype ID : saptrainers

Why PHP ?

2009-07-26T11:00:00.002+05:30

> PHP runs on different platforms (Windows, Linux, Unix, etc.)

> PHP is compatible with almost all servers used today (Apache, IIS, etc.)

> PHP is FREE to download from the official PHP resource: www.php.net

> PHP is easy to learn and runs efficiently on the server side

What is MYSQL ?

2009-07-26T10:56:00.003+05:30

> MySQL is a database server

> MySQL is ideal for both small and large applications

> MySQL supports standard SQL

> MySQL compiles on a number of platforms

> MySQL is free to download and use

PHP + MYSQL

> PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

What is PHP & PHP FILE ?

2009-07-26T10:50:00.006+05:30

PHP is a server-side scripting language.

Before you getting started with PHP you should have a basic understanding of the following:

> HTML/XHTML
> JavaScript

PHP stands for PHP: Hypertext Preprocessor

PHP is a server-side scripting language, like ASP

PHP scripts are executed on the server

PHP supports many databases (MySQL, Informix, Oracle, Sybase, Solid, PostgreSQL, Generic ODBC, etc.)

PHP is an open source software

PHP is free to download and use

PHP FILE

> PHP files can contain text, HTML tags and scripts
> PHP files are returned to the browser as plain HTML
> PHP files have a file extension of ".php", ".php3", or ".phtml"

Perform Case Sensitive Searches in SQL Server

2009-07-25T13:59:00.003+05:30

By default, SQL Server 7.0 installation sets up SQL Server to run in a case insensitive mode. For most applications this may work great, but there are certain situations were case sensitive searches are required. For instance, if a web site needs to have passwords that are case sensitive a method needs to be devised to perform case-sensitive queries. The following script performs a case-sensitive search.

First let’s create a table:

CREATE TABLE test
(
id INT NOT NULL,
msg VARCHAR(100) NOT NULL
)

Now let’s insert some case sensitive data into it:

INSERT INTO test (id, msg) VALUES (1, 'bUSY');
INSERT INTO test (id, msg) VALUES (2, 'BUSY');
INSERT INTO test (id, msg) VALUES (3, 'busy');

In our test we are searching for a ‘bUSY’ value in the msg column of the test table. So the syntax of the same query, if the SQL Server was set to be case sensitive, would be:

SELECT * FROM test where msg = ‘bUSY’;

This query will return all rows in the test table. Now, here is the script that will perform the case sensitive search.

DECLARE @table VARCHAR( 30 )
DECLARE @col VARCHAR( 30 )
DECLARE @searchVal VARCHAR( 195 )

SET @table = 'test'
SET @col = 'msg'
SET @searchVal = 'bUSY'

DECLARE @sql VARCHAR( 255 )
DECLARE @colLength VARCHAR( 3 )

SELECT @colLength = CONVERT( varchar(3), DATALENGTH( @searchVal ) )

SELECT @sql = 'SELECT * ' +
' FROM ' + @table +
' WHERE' +
' CONVERT( varbinary( ' + @colLength + '), ' + @col + ') = ' +
' CONVERT( varbinary( ' + @colLength + '), "' + @searchVal + '")' +
' AND ' + @col + '="' + @searchVal + '"'

EXEC( @sql )

Mutating and Constraining table?

2009-07-25T13:55:00.002+05:30

"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

There are several restrictions in Oracle regarding triggers:

• A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .

• A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

Loop through tables in PL/SQL

2009-07-25T13:47:00.001+05:30

Look at the following nested loop code example.

DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is '||emp_rec.ename);
END LOOP;
END LOOP;
END;
/

Executing an operating system command from PL/SQL

2009-07-25T13:44:00.002+05:30

There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe.

In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable.

What is the result of comparing NULL with NULL?

2009-07-25T13:42:00.001+05:30

NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL.

Look at this code example to convince yourself.

declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;

Difference between %TYPE and %ROWTYPE?

2009-07-25T13:40:00.001+05:30

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor.

Example:
DECLARE
v_EmpRecord emp%ROWTYPE;

%TYPE is used to declare a field with the same type as that of a specified table's column.

Example:
DECLARE
v_EmpNo emp.empno%TYPE;

use Dynamic SQL statements from PL/SQL

2009-07-25T13:35:00.003+05:30

Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples.

Note that statements are NOT terminated by semicolons:

EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

-- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements.

Look at these examples:

CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/

More complex DBMS_SQL example using bind variables:

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/

DDL statements from PL/SQL

2009-07-25T13:33:00.003+05:30

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDIATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).

begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;

NOTE: The DDL statement in quotes should not be terminated with a semicolon.

Read/write files from PL/SQL?

2009-07-25T13:27:00.003+05:30

Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.

Copy this example to get started:

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/

Print the screen from PL/SQL

2009-07-25T13:18:00.001+05:30

One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command.

For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/

DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000

If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.

How to Protect PL/SQL Code

2009-07-25T13:15:00.001+05:30

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.

This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.

The syntax is:

wrap iname=myscript.sql oname=xxxx.plb

How to keep a history of PL/SQL code changes?

2009-07-25T13:11:00.003+05:30

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes.

Look at this example:

CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors

Search PL/SQL code for a string/ key value?

2009-07-25T13:08:00.000+05:30

The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.

SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';

How can one see if somebody modified any code?

2009-07-25T13:05:00.002+05:30

Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view.

Example:

SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';

Should one use PL/SQL or Java to code procedures and triggers?

2009-07-25T13:02:00.002+05:30

Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:

PL/SQL:
• Data centric and tightly integrated into the database
• Proprietary to Oracle and difficult to port to other database systems
• Data manipulation is slightly faster in PL/SQL than in Java
• Easier to use than Java (depending on your background)

Java:
• Open standard, not proprietary to Oracle
• Incurs some data conversion overhead between the Database and Java type systems
• Java is more difficult to use (depending on your background)

What is PL/SQL ?

2009-07-25T12:59:00.001+05:30

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

Prevent Unnecessary Database Hit in ADO.NET

2009-07-23T13:18:00.000+05:30

One of the most useful of all features of ADO.NET is that you can attach messages to each row of data in a DataSet object. The SqlDataAdapter class attaches error messages to the rows of a DataSet if a specific database action has not been successfully completed. We can then check whether there are any errors in a DataSet prior to sending the same for updating the database using the HasErrors property of the DataSet instance. This, if used judiciously, can prevent an unnecessary database hit. Please refer to the code snippet that follows:

DataSet newDataSet = previousDataSet.GetChanges(DataRowState.Modified);

if (newDataSet.HasErrors)
{
// If there are errors take appropriate action
}

else
{

// Necessary code to update the database
}

USING the "Using" Statement in .NET

2009-07-23T13:14:00.002+05:30

The using statement can be used to specify a boundary for the object outside of which, the object is destroyed automatically. The runtime invokes the Dispose method of the objects that are specified within this statement when the control comes out of this block. This is why this is a preferred choice when using exceptions for managing resources in .NET. Refer to the following code that uses the “using” statement:

string connectionString = ...; // Some connection string

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some code
}

Note that when the end of the using block would be encountered, the Dispose () method will be immediately called on the instance. Note that when the Dispose() method is called on this connection instance, it checks to see if the connection is opened; if open it would close it implicitly prior to disposing off the instance.

The above code gets translated implicitly to:

string connectionString = ...; // Some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString));

try
{
sqlConnection.Open();
//Some code
}

finally
{
((IDispose)sqlConnection).Dispose();
}

Remember to keep the try block as short as possible. Note that in the code example above, we have just opened the connection in the try block. Do not use any unnecessary code/logic in the try block that is not supposed to throw any exception. Do not catch any exception that you cannot handle and avoid rethrowing exceptions unnecessarily as it is very expensive.

Exceptions in ADO.NET 2.0

2009-07-23T13:11:00.003+05:30

What are Exceptions?

An exception is an error that occurs at runtime. If it is not handled properly, it terminates the normal flow of the program. According to MSDN, “An exception is any error condition or unexpected behavior encountered by an executing program. Exceptions can be raised because of a fault in your code or in code you call (such as a shared library), unavailable operating system resources, unexpected conditions the common language runtime encounters (such as code that cannot be verified), and so on. Your application can recover from some of these conditions, but not others. While you can recover from most application exceptions, you cannot recover from most runtime exceptions.”

The .NET runtime creates an exception object to represent an exception when it occurs. It also creates an Exception Information Table for each executable. There are four different types of exception handlers, namely

A finally handler
A fault handler
A type-filtered handler
A user-filtered handler

Exceptions in ADO.NET

In ADO.NET 1.1, there was no common base class for ADO.NET exceptions. According to Frans Bouma, "every provider has its own exception for reporting errors, and they're not derived from a generic ADO.NET exception. This thus makes it hard to catch db specific exceptions in generic code and handle it". With ADO.NET 2.0 however, we have a new common exception base class called the System.Data.Common.DbException class. The generic DbException class in ADO.NET 2.0 exposes all the information a provider specific exception would require. This class enables catching the provider specific exceptions in a generic manner.

However, exceptions should be avoided at all cost. Exceptions are expensive; they slow down the application, and use resources, regardless of whether or not they are handled. In addition, "logic by exception" can make debugging very tedious.
Exception Handlers

Exceptions are handled using the try, catch and finally blocks. The try block contains code that raises an exception. The exceptions that are raised in the try block are caught in one or more appropriate catch blocks. Why appropriate? The reason is that if an exception has occurred, only one of the catch blocks would be executed that is most appropriate to the type of the exception that has occurred. The finally block contains any necessary cleanup code. It should be noted that a try block should contain one or more catch blocks or at least a finally block. Why is a finally block required?

A finally block is executed whether or not an exception occurs. Hence, it is particularly useful for cleanup operations. MSDN states, “the purpose of a finally statement is to ensure that the necessary cleanup of objects, usually objects that are holding external resources, happens immediately, even if an exception is thrown”. Let us consider that we have opened a database connection in a try block. If an exception occurs, it is caught in the catch block just after the try block as shown in the code snippet below:

string connectionString = ...; // Some connection string
SqlConnection sqlConnection = null;
try
{
sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
//Some code
}

catch (Exception ex)
{
//Some exception handling code
}

finally
{
sqlConnection.Close();
}

Remember that the connections that are opened as late as possible and released as early as possible. They should be closed immediately we are done using them. Database connections should be closed as soon as you are done using them for efficient connection pooling and hence enhanced performance. It is advisable to open the database connections in the try block and close them in the finally block. This would ensure that the connections would be properly closed irrespective of whether an exception has occurred or not. In the code example above, the connection is guaranteed to be closed as we have made a call to the Close() method on the SqlConnection instance in the finally block. I would however prefer to use the “using” statement for handling exceptions when using unmanaged resources.

Why ADO.NET 2.0

2009-07-23T13:09:00.000+05:30

With a view to mitigate the performance drawbacks of the earlier version of ADO.NET and add more flexibility, Microsoft has introduced more new features in ADO.NET 2.0 — features that have profound differences from ADO.NET 1.1, its earlier counterpart.

The performance of DataSets with high volumes of data has posed performance problems in the earlier version of ADO.NET. With ADO.NET 2.0 however, the indexing engine of the DataTable has been completely re-written to facilitate much faster processing of data. According to MSDN, "The indexing engine for the DataTable has been completely rewritten in ADO.NET 2.0 and scales much better for large datasets. This results in faster basic inserts, updates, and deletes, and therefore faster Fill and Merge operations". There have been numerous enhancements to the DataSet and the DataTable classes too.

What is new in ADO.NET 2.0

2009-07-23T13:02:00.003+05:30

ADO.NET 2.0 — The new features

The salient features of ADO.NET 2.0 are, but are not restricted to:

* Enhancements to the DataSet and Datatable classes
* Optimized DataSet Serialization
* Conversion of a DataReader to a DataSet or a DataTable and vice versa
* Data Paging
* Batch Updates — Reduction in database roundtrips
* Asynchronous Data Access
* Common Provider Model
* Bulk Copy

Enhancements to the DataSet and the DataTable classes

In ADO.NET 2.0, the DataTable supports two methods Load and Save. Note that the DataSet class supports both these methods from the earlier version of ADO.NET. While the Load method can load data from XML into the DataTable instance, the Save method can persist the DataTable instance to a persistence storage media. Further, unlike in ADO.NET 1.1, the DataTable class now supports serialization. It should be noted that in ADO.NET 1.1, only the DataSet class supported serialization. Moreover, the DataTable class in ADO.NET 2.0 contains a method called CreateTableReader that returns a DataTableReader instance that can be used not only to read forward only data but also in a disconnected mode of operation.

Further, the DataTable class in ADO.NET 2.0 is serializable, unlike in ADO.NET 1.1 where one had to store the DataTable instance inside a DataSet instance to make it serializable.

Optimized DataSet Serialization

The DataSet object in ADO.NET is an in-memory representation of disconnected, cached set of data and provides a consistent relational programming model regardless of the data source. When we require sending a DataSet class instance across process boundaries, the DataSet needs to be serialized. But, what is Serialization? Serialization is the process of converting an in-memory object into a serial stream of bytes. Serialization and De-Serialization is mostly used to transport objects or to persist the state of the objects to a persistent storage media (e.g. to a file or database).

Unfortunately, the DataSet class in the earlier version of ADO.NET used to serialize data as XML even if the BinarySerializer was specified. This resulted in slower serialization and an overhead of large sized serialized data. In ADO.NET 2.0 however, DataSet serialization has been improved to a large extent and you can now use the RemotingFormat property of the DataSet class to specify that the data is to be stored directly in binary format. Hence, the DataSet class ADO.NET 2.0, unlike ADO.NET 1.1, supports both XML and Binary Serialization formats. This is shown in the code example below.

BinaryFormatter binaryFormatter = new BinaryFormatter();

FileStream fileStream = new FileStream("c:\\emp.dat", FileMode.CreateNew);

DataSet empDataSet = GetEmployeeDataSet(); //This is a custom method that creates, populates and then returns a DataSet instance.

empDataSet.RemotingFormat = SerializationFormat.Binary;

//Serialize the employee Data Set instance as binary. In order to serialize the
//same instance as XML, specify SerializationFormat.XML

binaryFormatter.Serialize(fileStream,empDataSet);

fileStream.Close();

Conversion of a DataReader to DataSet or DataTable and vice-versa

ADO.NET 2.0 allows loading a DataReader object into a DataSet or a DataTable and vice versa. Both the DataSet and the DataTable classes in ADO.NET 2.0 contain the Load method that can be used to load a DataReader instance into a DataSet or a DataTable.

The following piece of code shows how a DataTable can be loaded in a DataReader instance.

string connectionString = ....; //Some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString);

sqlConnection.Open();

SqlCommand sqlCommand = new SqlCommand("Select * from Employee", sqlConnection);

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

DataTable dataTable = new DataTable("Employee");

dataTable.Load(sqlDataReader);

The GetDataReader method of both the DataSet and the DataTable classes can be used to retrieve a DataReader instance from either a DataSet or a DataTable. If ithe DataSet instance on which the method is called contains multiple DataTable instances, the resultant DataReader would also contain multiple resultsets.

Data Paging

Data Paging is a very powerful feature in ADO.NET. It can be recollected that in the earlier version of ADO.NET we needed to make use of stored procedures for incorporating Data Paging functionality in our applications. Now, with ADO.NET 2.0, it is much simplified with the introduction of the ExecutePageReader method in the SqlDataReader class. The following code snippet illustrates how this feature can be achieved.

string connectionString = ....; //some connection string

SqlConnection sqlConnection = new SqlConnection(connectionString);

sqlConnection.Open();

SqlCommand sqlCommand = new SqlCommand("Select * from Employee", sqlConnection);

SqlDataReader sqlDataReader = sqlCommand.ExecutePageReader(CommandBehavior.CloseConnection, 1, 25);

Batch Updates — Reducing Database Roundtrips

Database round trips degrade the performance of applications to a large extent due to increased network traffic. The Batch update of ADO.NET 2.0 promises to improve the performance of applications to a large extent by reducing the number of round trips to the database. In the earlier version of ADO.NET, if we made any changes to the DataSet and then saved the DataSet using the Update method of the DataAdapter class, it made round trips to the database for each modified row in the DataSet. This was a major performance hindrance.

But, how does it know which row or rows have been changed? When we make changes to a DataRow, its RowState changes to reflect the change. Now, for every row that has been changed (depending on whether the RowState property has been changed or not), the DataAdapter communicates with the database in such a type of operation. This is a terrible performance drawback with large volumes of data. In ADO.NET 2.0 however, there is a property known as UpdateBatchSize that can be used to specify a group or batch of rows for a particular hit to the database. It provides the number of rows to be updated in a batch. In other words, the UpdateBatchSize property of the DataAdapter class actually determines the number of changed rows changed rows that send to the database server in a single operation.

Asynchronous Data Access

In the earlier version of ADO.NET, the ExecuteReader, ExecuteScalar and the ExecuteNonQuery methods used to block the current executing thread. However, ADO.NET 2.0 supports asynchronous data access mode.In ADO.NET 2.0, these methods come with Begin and End methods that support asynchronous execution.

The Common Provider Model

In the earlier version of ADO.NET, if we wanted to implement a provider independent Data Access Layer, we had to implement the Factory Design Pattern where a class would have been responsible for returning the specific type of Command,Data Reader, DataAdapter or Connection. In ADO.NET 2.0 we can create provider-independent data access code even without referencing the provider-specific classes using the System.Data.Common namespace that exposes a number of factory classes.

The DbProviderFactory class contains two methods called the GetFactoryClasses method and the Getfactory method. While the former is responsible for retrieving all the providers supported, the later can be used to retrieve the specific provider. Refer to the code snippet below that demonstrates how we can make use of this class to create a sql connection seamlessly.

DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbConnection dbConnection = dbProviderFactory.CreateConnection();

Bulk Copy Feature

In the earlier version of ADO.NET, copying a large volume of data from a source data store to a destination table in SQL database had performance drawbacks due to the repeated database accesses that were required. The SqlBulkCopy feature in ADO.NET 2.0 enables us to copy a large volume of data between a source data store and a destination data table. This class can be used to specify the source and the target data sources for this copy operation. The following code snippet illustrates how this feature can be implemented.

SqlConnection connectionObjSource = new SqlConnection(Conn_str);

connectionObjSource.Open();

SqlConnection connectionObjTarget = new SqlConnection(Conn_str1);

connectionObjTarget.Open();

SqlCommand sqlCommand = new SqlCommand("Select * from Employee", connectionObjSource);

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

DataTable dt = new DataTable("Employee");

SqlBulkCopy sqlBulkcopy = new SqlBulkCopy(connectionObjTarget);

bulkcopy.DestinationTableName = "Employees";

bulkcopy.WriteToServer(sqlDataReader);

Transactions in ADO.NET

2009-07-23T12:52:00.004+05:30

What is a Transaction

A transaction is a group of operations combined into a logical unit of work that is either guaranteed to be executed as a whole or rolled back. Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing is an indispensible part of ADO.NET. It guarantees that a block of statements will either be executed in its entirety or rolled back,( i.e., none of the statements will be executed). Transaction processing has improved a lot in ADO.NET 2.0. This article discusses how we can work with transactions in both ADO.NET 1.1 and 2.0 versions.

Implementing Transactions in ADO.NET

Note that in ADO.NET, the transactions are started by calling the BeginTransaction method of the connection class. This method returns an object of type SqlTransaction. Other ADO.NET connection classes like OleDbConnection, OracleConnection also have similar methods. Once you are done executing the necessary statements within the transaction unit/block, make a call to the Commit method of the given SqlTransaction object, or you can roll back the transaction using the Rollback method, depending on your requirements (if any error occurs when the transaction unit/block was executed).
To work with transactions in ADO.NET, you require an open connection instance and a transaction instance. Then you need to invoke the necessary methods as stated later in this article. Transactions are supported in ADO.NET by the SqlTransaction class that belongs to the System.Data.SqlClient namespace.

The two main properties of this class are as follows:

* Connection: This indicates the SqlConnection instance that the transaction instance is associated with
* IsolationLevel: This specifies the IsolationLevel of the transaction

The following are the methods of this class that are noteworthy:
Commit() This method is called to commit the transaction
Rollback() This method can be invoked to roll back a transaction. Note that a transaction can only be rolled back after it has been committed.
Save() This method creates a save point in the transaction. This save point can be used to rollback a portion of the transaction at a later point in time. The following are the steps to implement transaction processing in ADO.NET.

* Connect to the database
* Create a SqlCommand instance with the necessary parameters
* Open the database connection using the connection instance
* Call the BeginTransaction method of the Connection object to mark the beginning of the transaction
* Execute the sql statements using the command instance
* Call the Commit method of the Transaction object to complete the
transaction, or the Rollback method to cancel or abort the transaction
* Close the connection to the database

The following code snippet shows how we can implement transaction processing using ADO.NET in our applications.

string connectionString = ...; //Some connection string
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

try
{
sqlCommand.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)";
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
//Usual code
}

catch(Exception e)
{
sqlTransaction.Rollback();
//Usual code
}

finally
{
sqlConnection.Close();
}

The next piece of code illustrates how we can use the “using” statement for the above code. According to MSDN, the "using" statement, "defines a scope, outside of which an object or objects will be disposed. A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement".

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
sqlConnection.Open();
transaction = sqlConnection.BeginTransaction();

command.Transaction = transaction;

command.CommandText = "Insert into employee (empID, empName) values (1, 'Joydip');
command.ExecuteNonQuery();

command.CommandText = "Insert into dept (deptID,deptName,empID) values (9,'Software',1)";
command.ExecuteNonQuery();

transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
sqlConnection.Close();
}
}

The Microsoft's ADO.NET version 2.0 added a lot of new features to its earlier counterpart to add moer flexibility and ease of use. As far as transactions are concerned, a new namespace called System.Transactions has been introduced that promises a significantly improved support for distributed transactions. It contains a class called TransactionScope that can run a set of statements. It can also determine whether the objects in the scope have support for transactions. If the transaction has completed successfully, the changes are committed to the database else it is rolled back. We need to specify whether the transaction block is complete by making a call to the TransactionScope.Complete method explicitly, else, the transaction would be rolled back when the transaction instance would be discarded by the implicit Dispose method.
The following piece of code illustrates what we have learnt so far in this section.

bool IsConsistent = false;
using (System.Transactions.TransactionScope transactionScope = new System.Transactions.TransactionScope())
{
SqlConnection sqlConnection = newSqlConnection(connectionString);
string sqlString = "Update emp set empName = 'Joydip Kanjilal' where empID = 9";
SqlCommand cmd1 = newSqlCommand(sql, cn);
sqlConnection.Open();
cmd1.ExecuteNonQuery();
sqlConnection.Close();
transactionScope.Consistent = IsConsistent;
}

TransactionScope also has support for distributed transactions. We can implement transactions for multiple database connections using it. The following piece of code shows how we can implement transactional support for multiple databases using the TransactionScope class.

using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection codesDatabaseConnection = new SqlConnection(codesDatabaseConnectionString))
{
SqlCommand sqlCommandCodes = codesDatabaseConnection.CreateCommand();
sqlCommandCodes.CommandText = "Insert Into codes (codeID,codeText) values (1,'Test')";
codesDatabaseConnection.Open();
sqlCommandCodes.ExecuteNonQuery();
codesDatabaseConnection.Close();
}

using (SqlConnection statesDatabaseConnection = new SqlConnection(statesDatabaseConnectionString))
{
SqlCommand sqlCommandStates = statesDatabaseConnection.CreateCommand();
sqlCommandStates.CommandText = "Insert into States(stateID,stateName) values (1, 'Test')";
codesDatabaseConnection.Open();
sqlCommandStates.ExecuteNonQuery();
statesDatabaseConnection.Close();
}

transactionScope.Complete();
}

Points to be noted

It should be noted that the SqlTransaction object returned by the BeginTransaction () method has to be assigned to the Transaction property of the Command object; else an InvalidOperationException will be thrown by the application when the first query is executed. Likewise, the Connection instance should be open by invoking the Open method on it prior to starting a new transaction; else an InvalidOperationException would be thrown. In order to improve the performance of applications, we should try to keep the transactions (the transaction units/blocks that contain the statements to be executed in a batch as a whole) as short as possible. This will help minimize the lock contention and hence increase throughput. Further, we should analyze whether or not we actually require a transaction for a batch of statements. Try not to unnecessarily have transactional statements in you code as it might have a performance drawback due to the reasons stated above.

Conclusion

The usage of transactions guarantee the execution of a batch of statements sequentially in its entirety or roll them back hence preserving the database integrity and data consistency. In a nutshell, in order to work with transactions, invoke the BeginTransaction() method of the appropriate database connection instance and then call either the Commit() or Rollback() method on the returned transaction object reference depending on the circumstances. However, it should be noted that transactions hold locks and may cause contention issues; they should be as short as it is possible. Hence, this is a major performance drawback in using transactions in our code. But, if used properly, it can facilitate the design and implementation of robust applications with data security and consistency. This article has discusses transactions in details in a lucid language with code examples to illustrate the concepts.

Date Time Using Javascript

2009-07-23T12:29:00.005+05:30

Hi Buddies,

To display Date time on your web page copy this script and paste where ever you want to display

<script>
var mydate=new Date()
var year=mydate.getYear()
if (year < 1000)
year+=1900
var day=mydate.getDay()
var month=mydate.getMonth()
var daym=mydate.getDate()
if (daym<10)
daym="0"+daym
var dayarray=new Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat")
var montharray=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
document.write("<P ALIGN=RIGHT> <FONT color=white SIZE=-3 weight=bold FACE=VERDANA,ARIAL>" + montharray[month]+" "+daym+", "+year+ "</FONT>");
 <script>

Getting Started with Stored Procedures

2009-07-23T12:22:00.002+05:30

To begin writing stored procedures, the following are essential:

1. A database management system.

2. A database built inside the database management system (see the end of this article for a sample).

3. A text editor, such as Notepad or Query Analyzer.

Items 1 and 2 are absolutely essential. You can’t write stored procedures without a database. They would be useless. Sometimes, I write my procedures in Notepad (or another text editor), and copy them into the New Stored Procedure window in SQL Server. The New Stored Procedure window is a bit small, and in Notepad I can spread things out a bit (you’ll see later).

Next, you will have to decide what you want your stored procedure to do. It can be tempting to just dive right into the task at hand, but it is always prudent to sketch out some ideas first. Some considerations should be:

* Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?

* With which tables will you have to work? Does it make sense to create a VIEW first?

* How often will this procedure actually be used?

Once you have struggled with these questions (something of an exaggeration, I guess), you will be ready to start coding!

What are Stored Procedures

2009-07-23T12:21:00.001+05:30

Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command.

An example is:

exec usp_displayallusers

The name of the stored procedure is "usp_displayallusers", and "exec" tells SQL Server to execute the code in the stored procedure. (Note: "usp_" in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as:

SELECT * FROM USERLIST

This "select" statement will return all data in the USERLIST table. You may think, skeptically, that stored procedures aren’t terribly useful. Just save the query and run it when you need to. Too easy, right?

Well, there is more to the story. Many queries get more complex than "select * from . . ." Also, you may want to call the stored procedure from an application, such as an ASP page, Visual Basic application, or a Java servlet. With a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to J2EE, you only need to change the application layer, which will be significantly easier. Much of the business logic will remain in the database.

Enough background—let’s write some stored procedures.

Benefits of Stored Procedures

2009-07-23T12:15:00.003+05:30

Why should you use stored procedures? Let's take a look at the key benefits of this technology:

a) Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

b) Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

c) Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.

d) Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Stored procedures are very similar to user-defined functions, but there are subtle differences.

Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.

Example

Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:

SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'

This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.

We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory.

EXECUTE sp_GetInventory 'NY'

Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.

Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -- you'll be amazed!

SQL Server Performance Tuning Tips - IV

2009-07-22T00:33:00.003+05:30

Performance Tuning Tips for Creating Visual Basic Applications

52. While ADO (and other VB object libraries) make database manipulation easy for the programmer, using these shortcuts can kill SQL Server performance. As a rule of thumb, encapsulate your DML (Data Manipulation Language) in stored procedures and run them from your VB application. This bypasses object library overhead (such as reducing cursors) and reduces the chatter between the VB application and SQL Server over the network.
So what does this mean in practice? Essentially, avoid using the ADO recordset object to modify (INSERT, UPDATE, DELETE) data in your VB code. Instead, use Transact-SQL, encapsulated in stored procedures, to modify data in a SQL Server database. An ADO recordset should be used as a method of reading data, not modifying data. [6.5, 7.0, 2000]

53. When using an ADO recordset to return data from SQL Server, the most efficient way is to use what is often called a firehose cursor. The firehouse cursor is really an incorrect term because it is not a cursor. A firehose cursor is just a method to quickly move data from SQL Server to the client that requested it.
Essentially, a firehose cursor sends the requested data (from the query) to an output buffer on SQL Server. Once the output buffer is full, it waits until the client can retrieve the data from the output buffer. Then the output buffer is filled again. This process repeats over and over until all of the data is sent to the client. Another advantage of this method is that records are only locked long enough to be moved to the output buffer.
When you open an ADO RecordSet and use its default settings, a firehose cursor is automatically used by default. If you want to specify a firehouse cursor manually, you can do so by using these property settings:
• CursorType = adForwardOnly
• CursorLocation = adUseServer
• LockType = adLockReadOnly
• CacheSize = 1
When the client receives the data from the firehose cursor, the data should be read into a local data structure for local use by the client. [6.5, 7.0, 2000]

54. When accessing data on a SQL Server, write your VB code so as to minimize the number of round-trips between the application and SQL Server. Each and every time you use ADO to execute Transact-SQL code to get data from SQL Server, multiple, time-consuming steps have to occur. For example:
• Your VB code must generate a request to SQL Server in the form of a Transact-SQL statement.
• The statement is sent to the database through the Connection object.
• The request from the Connection object has to be translated into packets that can be sent over the network.
• The packets move over the network.
• When the packets arrive at SQL Server, they must be converted back into a form useable by SQL Server.
• SQL Server must then process the Transact-SQL statement. Assuming a stored procedure is not used, then this code must be optimized and compiled, then executed.
• The results, in the form of TDS (Tabular Data Stream), are then translated into packets that can be sent over the network.
• The packets move over the network, again.
• When the packets arrive at SQL Server, they must be converted back into TDS format.
• When ADO received the TDS data, it is converted into a recordset, ready to be used by the application.
If you know much about the technical details of networking, then you know that the above steps have been oversimplified. The point to remember is that round-trips between your application and SQL Server are expensive in time and resources, and you need to do your best in your code to minimize them. [6.5, 7.0, 2000]

55. One way to help reduce round-trips between your application and SQL Server is to move the data you need at the client from SQL Server in a single query, not in multiple queries. I have seen some applications that only retrieve one row at a time, making a round-trip for every row needed by the application. This can be very expensive in resources and it hurts performance. Of course, you can't always know what rows will be needed ahead of time, but the better you can guess, even if you guess and return too many rows, returning them in one round-trip is usually more efficient than retrieving only one row at a time. [6.5, 7.0, 2000]

56. When retrieving data from a SQL Server 7 database, take full advantage of views when appropriate. This is especially true if you are not encapsulating your Transact-SQL in stored procedures as recommended. While calling a view is not usually as efficient as using a stored procedure to retrieve data, it is much more efficient that using embedded Transact-SQL in your ASP code or COM components. [6.5, 7.0, 2000]

57. Don't use DAO to access SQL Server, it is performance suicide. Also avoid ODBCDirect. Instead, use RDO or ADO. [6.5, 7.0, 2000]

58. When creating a connection using ADO, be sure you use the OLE DB provider, not the older ODBC provider for SQL Server, or the ODBC provider for OLE DB. The parameter you will use in your connection string is "provider=sqloledb". The OLE DB provider performs much more efficiently than the ODBC provider, providing better performance. [7.0, 2000]

59. Use stored procedures instead of embedding Transact-SQL in your VB code. This significantly reduces network traffic and speeds up query execution. [6.5, 7.0, 2000]

60. If you are VB developer and need to access SQL Server data, but don't have the time or interest in learning how to write stored procedures, consider using the GetRows method of the RecordSet object. The GetRows method is used to pull all the records from the recordset into an array, which is much faster than using embedded Transact-SQL to download a RecordSet to your application. [6.5, 7.0, 2000]

61. If possible in your application, use stored procedures to "batch" a set of related Transact-SQL statements together, instead of calling a separate stored procedure for every database task you want to perform. This helps to reduce network traffic and server overhead. [6.5, 7.0, 2000]

62. If you have a related group, or batch, or Transact-SQL statements you want to execute, but you don't want to use a stored procedure, as generally recommended for dealing with batches of Transact-SQL statements, one option you can use to boost performance if your VB code is to concatenate two or more separate Transact-SQL statements into a single batch and execute them as a single message. This is much more efficient that sending the Transact-SQL code to SQL Server as many different messages. [6.5, 7.0, 2000]

63. When SELECTing data from your application to be returned to it from SQL Server, limit the amount of rows returned to only those that are needed now. If necessary, force the user to enter selection criteria to limit the results set. [6.5, 7.0, 2000]

64. If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data they don't need, consider using the TOP operator within the query. This way, you can limit how may rows are returned, even if the user doesn't enter any criteria to help reduce the number or rows returned to the client. [6.5, 7.0, 2000]

65. If your application needs to perform looping, try to put the loop inside a stored procedure so it can be executed on the server without having to make round trips between the client and server. [6.5, 7.0, 2000]

66. When creating transactions in your application, don't create them using ADO's methods. Instead, encapsulate the transaction in stored procedure so that it executes on the server. [6.5, 7.0, 2000]

67. Keep as much of the business logic of your application as possible off the client. In two-tier designs, put the business logic in stored procedures on the server. In n-tier designs, put the business logic in components on MTS. [6.5, 7.0, 2000]

68. If you have the need to filter or sort data on-the-fly at the client, let ADO do this for you at the client. When the data is first requested by the client from the server (ideally using a stored procedure), have all the data the client wants to "play" with sent to the client. Once the recordset is at the client, then ADO methods can be used to filter or sort the data. This helps to reduce network traffic and takes some of the load off of the server. [6.5, 7.0, 2000]

69. By default, the CacheSize property of the ADO Recordset object determines how many rows are fetched from a server-side cursor at a time. The default is one. This means each row of the recordset is returned one at a time from the server to the client. This is very inefficient. The CacheSize property needs to be set to a much higher figure, such as between 100 and 500, depending on the number of rows that are to be eventually returned from the server to the client. [6.5, 7.0, 2000]

70. When calling SQL Server stored procedures from the ADO Command object, don't use the Refresh method to identify the parameters of a stored procedure. This produces extra network traffic and slows performance. Instead, explicitly create the parameters yourself using ADO code. [7.0, 2000]

71. ADO allows you to create four different types of SQL Server cursors. Each has its own place, and you will want to choose the cursor that uses the least possible resources for the task at hand. When at all possible, attempt to use the Forward-Only cursor, which uses the least amount of overhead of the four cursor types. [6.5, 7.0, 2000]

72. Avoid using the MoveFirst method of the RecordSet object when using a Forward-Only cursor. In effect, when you use this method, it re-executes the entire query and repopulates the Forward-Only cursor, increasing server overhead. [6.5, 7.0, 2000]

73. If you create COM objects to encapsulate database access, try to follow these two suggestions if you want optimum speed: 1) use in-process dlls; and 2) use early-binding. [6.5, 7.0, 2000]

74. Consider using ADO's ability to create disconnected recordsets to help reduce the load on SQL Server. [6.5, 7.0, 2000]

75. When storing your SQL Server data into VB variables, always use strongly typed variables. Avoid using the variant data type (which is not always possible), as it has greater overhead than the other data types. [6.5, 7.0, 2000]

76. If you create object variables in your VB code to refer to COM objects that hold SQL Server data, be sure to strongly type them. Avoid using the AS OBJECT keywords, instead, always explicitly specify the type of object you want to create. [6.5, 7.0, 2000]

77. When instantiating COM objects to hold SQL Server data, create them explicitly, not implicitly. [6.5, 7.0, 2000]

78. If you will be calling the same stored procedure, view, or SQL statements over and over again in your code, don't create a new Command object each time. Instead, reuse the Command object. [6.5, 7.0, 2000]

79. When looping through recordsets, be sure you bind columns to field objects before the looping begins. Don't use the Fields collection of the Recordset object to assign values for fields in a Recordset within each loop, it incurs much more overhead. [6.5, 7.0, 2000]

80. If you know that the results of a query from within a stored procedure you call will return only one row of data (and not an entire recordset), don't open an ADO Recordset for the purpose of retrieving the data. Instead, use a stored procedure output parameter. [6.5, 7.0, 2000]

81. If your application needs to insert a large binary value into an image data column, perform this task using a stored procedure, not using an INSERT statement embedded in your application. The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time). Using a stored procedure avoids all this. [6.5, 7.0, 2000]

82. When ADO is used to open more than one ForwardOnly recordset on a single Connection object at a time, only the first recordset is opened using the Connection object you previously created. Additional new connections don't use the same Connection object. Instead, separate connections are created for each ForwardOnly recordset you create after the first. This occurs because SQL Server can only open one ForwardOnly cursor per connection. The more connections you create, the greater the stress on SQL Server and performance and scalability suffer.
To avoid this problem, don't use a ForwardOnly recordset. Static, Keyset, and Dynamic recordsets don't have this problem. Another option is to use a client side cursor instead of SQL Server cursor. Or you can close each recordset before opening another on the same connection. [6.5, 7.0, 2000]

83. When making your connection to SQL Server, choose DSN-less connections for the fastest connection. Not only does it make database connections faster, it allows you to use the OLE DB provider, which is not available using a DSN-based connection. The OLE DB provider is the fastest provider you can use to access SQL Server.
If you do need to use a DSN, select System DSNs over File DSNs, because they are faster when making connections. [7.0, 2000]

84. When creating a Connection object, always create it explicitly, not implicitly. Opening a Connection object explicitly consumes less resources than opening it implicitly, and it also allows you to more efficiently manage multiple connections and to reassign the various roles that the Connections objects perform within your application. [6.5, 7.0, 2000]

85. When using recordsets, be sure to open them explicitly, not implicitly. When recordsets are opened implicitly, you cannot control the default cursor and lock types, which are, respectively, forward-only and read-only. If you always open your recordsets explicitly, then you can specify which cursor and lock types you want to invoke for this particular situation, specifying the types with the least amount of overhead to accomplish the task at hand. [6.5, 7.0, 2000]

86. When using ADO to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of "connection creep". Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server's performance. [6.5, 7.0, 2000]

87. If you are connecting to SQL Server via either OLE DB (version 2.0 or higher) or ODBC (version 3.0 or higher), SQL Server connection pooling is automatically implemented for you. Because of this, you don't have to write special code to implement connection pooling yourself. In addition, you don't want to even reuse an ADO connection object, which is commonly done by many VB developers.
If you want to take the best advantage of database connection pooling, and optimize your VB application's SQL Server data access, the best advice you can receive is to be sure that you only open a database connection just before you need it, and then close it immediately after you are done with it. Don't leave database connections open if you are not using them.
When you create or tear down a database connection in your VB code, you aren't really creating a new connection or tearing down a current connection. What is happening is that your connection requests are send to OLE DB or ODBC, and they determine if a connection needs to be created or torn down. If a new connection is needed, then one is created, or one is used from the current connection pool. And if you request that a connection be torn down, it will actually pool the unused connection until it is needed, or tear it down if it is not reused within a given time period. [6.5, 7.0, 2000]

88. In order for connection pooling to work correctly, be sure each connection you open uses the same ConnectionString parameters. Connection pooling only works if all of the parameters for the ConnectionString are identical. If they are all not identical, then a new connection will be opened, circumventing connection pooling. [6.5, 7.0, 2000]

89. If appropriate for your application, locate the application's data access components on the SQL Server where the data is, instead of at the client. This can significantly reduce network traffic and overhead and boost data throughput. [6.5, 7.0, 2000]

90. When creating a Command object to execute a stored procedure against SQL Server, you can use either the adCmdText or the adCmdStoredProc property to tell ADO that you want to execute a stored procedure. Always use the adCmdStoredProc property, which uses RPCs between the client and SQL Server. This acts to bypass parameter translation and boosts performance from 20 to 30 percent over using the adCmdText property. [6.5, 7.0, 2000]

91. If you need to execute a stored procedure from a Command object, and if the stored procedure will not return any rows, you can boost performance of the Command object by setting the adExecuteNoRecords option. This tells the Command object to not ask for a returning rowset, which saves a little overhead and reduce memory usage. [6.5, 7.0, 2000]

92. If you need your VB application to generate a unique value for use in a primary key column in a SQL Server table, performance will be slightly better if you let SQL Server, instead of your VB application, create the unique value. SQL Server can generate unique keys using either an Identity (using the Integer data type) column or by using the NEWID function in a UniqueIdentifier column. Of these two, Identify columns offer better performance. [6.5, 7.0, 2000]

93. When creating COM components to access SQL Server, try to design the component to have as few properties as possible. For example, instead of having a property for every column of data you want to send back or forth between the database and your application, create one generic property that can be used to send all of the columns at one time. What this does is reduce the number of calls that must be made by the component, reducing overhead on the component and SQL Server. [6.5, 7.0, 2000]

94. When setting Connection Object properties, use the following dot notation instead of using the fully qualified object property notation, whenever appropriate, as it is faster.

Use this format for optimum speed:
WITH cn
.ConnectionTimeout = 100
.ConnectionString = "xyz"
.Cursor Location = adUseClient
END cn
Not this format:
cn.ConnectionTimeout = 100
cn.ConnectionString = "xyz"
cn.Cursor Location = adUseClient
If you are one of those people who need to prove this claim for yourself, try running the following code:

Public Declare Function GetTickCount Lib "kernel32" () As Long
Sub T1()
Dim i As Long
Dim cn As ADODB.Connection
Dim Fast As Long
Dim Slow As Long

Set cn = New ADODB.Connection

With cn
.ConnectionTimeout = 100
.ConnectionString = "xyz"
.CursorLocation = adUseClient
End With
cn.ConnectionTimeout = 100
cn.ConnectionString = "xyz"
cn.CursorLocation = adUseClient

For i = 1 To 100000
Fast = Fast - GetTickCount
With cn
.ConnectionTimeout = 100
.ConnectionString = "xyz"
.CursorLocation = adUseClient
End With
Fast = Fast + GetTickCount

Slow = Slow - GetTickCount
cn.ConnectionTimeout = 100
cn.ConnectionString = "xyz"
cn.CursorLocation = adUseClient
Slow = Slow + GetTickCount
Next
MsgBox "Fast=" & Fast & vbCrLf & "Slow=" & Slow
End Sub

Thanks to Gareth Edwards for the above example code.

[6.5, 7.0, 2000]

95. Don't use VB objects to act as a data holder (to store data) in your SQL Server-based applications. Instead, use an array or a collection of user-defined types (UDTs). While using objects you create to store data can be convenient, it also creates a lot of unnecessary overhead. Each time you have to instantiate and then destroy an object hurts performance and scalability. How do you know if an object you have created is storing data that should be stored elsewhere? If the class has mostly properties and few if any methods, then this is a good clue. [6.5, 7.0, 2000]

96. Don't instantiate, initialize, use, and then destroy an object within a loop. If the loop repeats itself much at all, you create an inordinate amount of overhead for your application. Instead, reuse the same object in the loop. One of the best ways to do this is to include a reinitialize method for the object that can be called from within the loop. [6.5, 7.0, 2000]

97. For maximum performance, consolidate most, if not all, of your COM components in a single DLL. COM components instantiated from a single DLL instantiate faster than if they are called from multiple DLLs. [6.5, 7.0, 2000]

98. If you decide not to use a stored procedure to access SQL Server, but instead choose to use an embedded SQL statement in your VB code, and if that embedded SQL statement will be repeated, such as in a loop, consider setting the ADO Command object's "Prepared" property to "True".
What this property does is to tell SQL Server to compile and save a copy of your SQL statement in SQL Server's cache. The first time the SQL statement is executed, the statement has to be compiled and stored in memory. But in subsequent calls, the statement is called from the cache, boosting performance because it does not have to be recompiled each time it is called.
If the SQL statement will only be executed once, then don't set this option, as it will actually decrease performance for SQL statements that are run only once. The performance boost only comes if the SQL statement is run multiple times. [7.0, 2000]

99. If you use collections in your VB 6 or ASP code, consider instead using dictionaries in order to boost the performance of your application. While you are probably familiar with what and how collections are used, you may not be familiar with dictionaries. The dictionary class (the Scripting.Dictionary Object) is part of the Microsoft Scripting Runtime library, which is delivered with the VB 6 and ASP development environments.
Like collections, dictionaries can hold any type of data, and items can be retrieved by using a key, or iterated using the "For Each" syntax. But dictionaries are different in that offer properties and methods not available for collections. And the biggest difference is that they are much faster, about twice as fast. If you haven't learned about dictionaries yet, you need to take the time now to learn about their numerous advantages. [6.5, 7.0, 2000]

100. To speed up string manipulation in VB (never a fast performing task), consider the following suggestions that can speed string manipulation performance:
• When feasible, use fixed-length strings instead of variable-length strings.
• Try to minimize string concatenation.
• Try to minimize string comparisons.
• Try to avoid calculating the length of a string more than once. If you need this data more than once, and the string length does not change, then calculate the length once and store this value in a variable for reuse.
• When passing a string to an in-process function, try to pass it by reference instead of by value.
• Most VB string functions have two forms, one that produces a variant and one that produces a string (generally has a "$" after the function name). The version that produces the string is faster, and should generally be used.
• Consider using byte arrays instead of strings.
[6.5, 7.0, 2000]

101. If you still have any legacy VB applications that still use VB-SQL to access SQL Server, you may want to consider rewriting the app. VB-SQL not only provides slow access, it is no longer supported by Microsoft. [6.5]

102. If you are the sort of VB developer who likes to design their applications around objects, you want to keep in mind that over-encapsulating data access within objects can hurt performance. For example, from an OO design approach, you might consider encapsulating data access to each individual table in a SQL Server database, creating a separate class for each table. While this may appeal to your OO design goals, it is inefficient from a performance perspective.
Too much encapsulation can lead to situations where you don't take advantage of SQL Server's built-in optimization abilities, it causes too many round-trips to the database, and it can use more database connections than absolutely required. Instead of over-encapsulating your data access in class, a more efficient approach is to use stored procedures to encapsulate your business logic. Stored procedures eliminate these three drawbacks. [6.5, 7.0, 2000]

103. If you use send Transact-SQL code as part of your VB ADO code directly to SQL Server, without using a stored procedure (which we don't recommend if you want best performance, use a stored procedure instead) you want ADO to execute the Transact-SQL code using the sp_executesql system stored procedure, not for ADO to create a temporary stored procedure in one step, execute the temporary stored procedure in the second step, and then drop the temporary stored procedure in a third step. As you can imagine, this is a total of three crosses of the network, and it can greatly increase overhead and hurt performance.
How do you know if your ADO code is behaving well? Use the SQL Server Profiler to trace the activity between your VP application and SQL Server. If you see that SQL Server is creating temporary stored procedures, and not using sp_executesql, then you need to review your ADO code, looking for ways to optimize it. [7.0, 2000]

104. Limit the amount of rows you return from a database to populate a pick-list or drop-down box. Lots of rows not only slows down your application, it also makes it less convenient for your user to select the item or items they need. Have you ever had to select from over 100 choices? It is not easy.
If you need to give your user a lot of choices, instead of displaying them in one large pick-list or drop-down list, provide a way for the user to filter out any options that are not applicable to them. For the best performance, perform the filtering at the client, not the SQL Server.

Ideally, you should use a stored procedure to retrieve the minimum amount of rows you need, then if there are still a lot of rows to to deal with (from the user's perspective), provide a mechanism for the user to filter the list using the various ADO methods available to use for local filtering. This reduces the number of round trips from the client to SQL Server, helping to boost performance. [6.5, 7.0, 2000]

SQL Server Performance Tuning Tips - III

2009-07-22T00:29:00.002+05:30

Continued from SQL server performance tuning tips - II

36. If your SELECT statement contains a HAVING clause, write your query so that the WHERE clause does most of the work (removing undesired rows) instead of the HAVING clause do the work of removing undesired rows. Using the WHERE clause appropriately can eliminate unnecessary rows before they get to the GROUP BY and HAVING clause, saving some unnecessary work, and boosting performance.
For example, in a SELECT statement with WHERE, GROUP BY, and HAVING clauses, here's what happens. First, the WHERE clause is used to select the appropriate rows that need to be grouped. Next, the GROUP BY clause divides the rows into sets of grouped rows, and then aggregates their values. And last, the HAVING clause then eliminates undesired aggregated groups. If the WHERE clause is used to eliminate as many of the undesired rows as possible, this means the GROUP BY and the HAVING clauses will have less work to do, boosting the overall performance of the query. [6.5, 7.0, 2000]

37. If you need to write a SELECT statement to retrieve data from a single table, don't SELECT the data from a view that points to multiple tables. Instead, SELECT the data from the table directly, or from a view that only contains the table you are interested in. If you SELECT the data from the multi-table view, the query will experience unnecessary overhead, and performance will be hindered. [6.5, 7.0, 2000]

38. If your application performs many wildcard (LIKE %) text searches on CHAR or VARCHAR columns, consider using SQL Server's full-text search option. The Search Service can significantly speed up wildcard searches of text stored in a database. [7.0, 2000]

39. The GROUP BY clause can be used with or without an aggregate function. But if you want optimum performance, don't use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster.
For example, you could write your query two different ways:
USE Northwind
SELECT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
GROUP BY OrderID
or
USE Northwind
SELECT DISTINCT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
Both of the above queries produce the same results, but the second one will use less resources and perform faster. [6.5, 7.0, 2000] Added 1-12-2001

40. Generally, it is better to perform multiple UPDATEs on records in one fell swoop (using one query), instead of running the UPDATE statement multiple times (using multiple queries).
For example, you could accomplish the same goal two different ways:
USE Northwind
UPDATE Products
SET UnitPrice = UnitPrice * 1.06
WHERE UnitPrice > 5

GO

USE Northwind
UPDATE Products
SET UnitPrice = ROUND(UnitPrice, 2)
WHERE UnitPrice > 5

GO

or

USE Northwind
UPDATE Products
SET UnitPrice = ROUND(UnitPrice * 1.06, 2)
WHERE UnitPrice > 5

GO

As is obvious from this example, the first option requires two queries to accomplish the same task as the second query. Running one query instead of two or more usually produces the best performance. [6.5, 7.0, 2000]

41. Sometimes perception is more important that reality. For example, which of the following two queries is the fastest:
• A query that takes 30 seconds to run, and then displays all of the required results.
• A query that takes 60 seconds to run, but displays the first screen full of records in less than 1 second.
Most DBAs would choose the first option as it takes less server resources and performs faster. But from many user's point-of-view, the second one may be more palatable. By getting immediate feedback, the user gets the impression that the application is fast, even though in the background, it is not.
If you run into situations where perception is more important than raw performance, consider using the FAST query hint. The FAST query hint is used with the SELECT statement using this form:
OPTION(FAST number_of_rows)
where number_of_rows is the number of rows that are to be displayed as fast as possible.
When this hint is added to a SELECT statement, it tells the Query Optimizer to return the specified number of rows as fast as possible, without regard to how long it will take to perform the overall query. Before rolling out an application using this hint, I would suggest you test it thoroughly to see that it performs as you expect. You may find out that the query may take about the same amount of time whether the hint is used or not. If this the case, then don't use the hint. [7.0, 2000]

42. Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, we can reduce I/O and boost our application's performance. [7.0, 2000] Added 3-9-2001 More info on derived tables.

43. When using the WHILE statement, don't avoid the use of BREAK just because some people consider it bad programming form. Often when creating Transact-SQL code using the WHILE statement, you can avoid using BREAK by moving a few lines of code around. If this works in your case, then by all means don't use BREAK. But if your efforts to avoid using BREAK require you to add additional lines of code that makes your code run slower, then don't do that. Sometimes, using BREAK can speed up the execution of your WHILE statements. [6.5, 7.0, 2000]

44. Computed columns in SQL Server 2000 can be indexed if they meet all of the following criteria:
• The computed column's expression is deterministic.
• The ANSI_NULL connection-level object was on when the table was created.
• TEXT, NTEXT, or IMAGE data types are not used in the computed column.
• The physical connection used to create the index, and all connections used to INSERT, UPDATE, or DELETE rows in the table must have these six SET options properly configured: ANSI_NULLS = ON, ANSI_PADDINGS = ON, ANSI_WARNINGS = ON, ARITHABORT = ON, CONCAT_NULL_YIELDS_NULL = ON, QUOTED_IDENTIFIER = ON, NUMERIC_ROUNDABORT = OFF.
[2000]

45. One of the advantages of using SQL Server for two-tier and three-tier applications is that you can offload much (if not most) of the data processing work from the other tiers and place it on SQL Server. The more work you can perform within SQL Server, the fewer the network roundtrips that need to be made between the various tiers and SQL Server. And generally the fewer the network roundtrips, the more scalable and faster the application becomes.
But in some applications, such as those than involve complex math, SQL Server has traditionally been weak. In these cases, complex math often could not be performed within SQL Server, instead it had to be performed on another tier, causing more network roundtrips than desired.
Now that SQL Server 2000 supports user-defined functions (UDFs), this is becoming less of a problem. UDFs allow developers to perform many complex math functions from within SQL Server, functions that previously could only be performed outside of SQL Server. By taking advantage of UDFs, more work can stay with SQL Server instead of being shuttled to another tier, reducing network roundtrips, and potentially boosting your application's performance.
Obviously, boosting your application's performance is not as simple as moving math functions to SQL Server, but it is one more new feature of SQL Server 2000 that developers can take advantage of in order to boost their application's scalability and performance. [2000]

46. When creating scalar user-defined functions, avoid as a matter of routine applying them to large result sets. This is because complex user-defined functions have the potential of involving high overhead, and there is no way to know for sure how they will affect performance when dealing with large result sets. Of course, if you know for sure that the user-defined function is not a performance problem, then using it is OK in larger results sets. You will want to test this theory before the user-defined function is put into production. [2000]

47. SQL Server 2000 offers a new data type called "table." Its main purpose is for the temporary storage of a set of rows. A variable, of type "table," behaves as if it is a local variable. And like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared. In most cases, a table variable can be used like a normal table. SELECTs, INSERTs, UPDATEs, and DELETEs can all be made against a table variable.
For best performance, if you need a temporary table in your Transact-SQL code, try to use a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them much faster. In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000]

48. Don't repeatedly reuse the same function to calculate the same result over and over within your Transact-SQL code. For example, if you need to reuse the value of the length of a string over and over within your code, perform the LEN function once on the string, and this assign the result to a variable, and then use this variable, over and over, as needed in your code. Don't recalculate the same value over and over again by reusing the LEN function each time you need the value, as it wastes SQL Server resources and hurts performance. [6.5, 7.0, 2000]

49. Most of you are probably familiar with the aggregate SUM() function and how it works. Occasionally, it would be nice if SQL Server had a PRODUCT() function, which it does not. While SUM() is used to sum a group a data, the theoretical PRODUCT() function would find the product of a group of data.
One way around the problem of there not being a PRODUCT() function in SQL Server is to use some combination of a cursor and/or temporary tables. As you can imagine, this would not be very efficient. A better choice would be to use a set-based function, like the theoretical PRODUCT() function.
With a little algebra, you can simulate a PRODUCT() function in SQL Server using the built-in SQL Server LOG10(), POWER(), and SUM() function working together. This is because logarithms allow you to find the product of numbers by summing them. This was how the products of large numbers were found before the days of calculators. (Are you old enough to remember using logarithm tables in school? I am. Ouch!)
Below is a very simple example of how you can use a combination of the LOG10(), POWER(), and SUM() functions in SQL Server to simulate a PRODUCT() function. You will probably want to modify it to meet your specific needs, such as to eliminate null data, zero data, or data that might be negative.
SELECT column_name1, POWER(10,SUM(LOG10(column_name2))) AS Product
FROM table_name
GROUP BY column_name1
For example, let's look at the following to see how this works.
Record 1 (1000, 2)
Record 2 (1000, 2)
Record 3 (1000, 2)
Record 4 (1001, 3)
Record 5 (1001, 3)
Our goal here is find the product of all the records where column_name1 = 1000 and to find the product of all the records where column_name_name1 = 1001. When the above query is run, we get these results:
1000, 8
1001, 9
What has happened is that where column_name1 = 1000 (which are the first three records in our sample data), the values in column_name2 (which are 2 and 2 and 2) are multiplied together to return 8. In addition, where column_name1 = 1001 (which are the last two records in our sample data), the values in column_name2 (which are 3 and 3) are multiplied together to return 9.
Creating your own PRODUCT() function produces much faster results than trying to accomplish the same task by using a cursor and/or temporary tables. [6.5, 7.0, 2000] Added 10-11-2001

50. Many developers choose to use an identify column at their primary key. By design, an identity column does not guarantee that that each newly created row will be consecutively numbered. This means they will most likely be occasional gaps in the identity column numbering scheme. For most applications, occasional gaps in the identity column present no problems.
On the other hand, some developers don't like these occasional gaps, trying to avoid them. With some clever use of INSTEAD OF triggers in SQL Server 2000, it is possible prevent these numbering gaps. But at what cost?
The problem with trying to force an identify column to number consecutively without gaps can lead to locking and scalability problems, hurting performance. So the recommendation is not to try to get around the identify column's built-in method of working. If you do, expect performance problems. [2000]

51. Avoid using variables in the WHERE clause of a query located in a batch file. Let's find out why this may not be a good idea.
First, let's look at the following code:
SELECT employee_id
FROM employees
WHERE age = 30 and service_years = 10
Assuming that both the age and the service_years columns have indexes, and the table has many thousands of records, then SQL Server's Query Optimizer will select the indexes to perform the query and return results very quickly.
Now, let's look at the same query, but written to be more generic, one that you might find in a generic batch file:
DECLARE @age int
SET @age = "30"
DECLARE @service_years int
SET @service_years = "10"
SELECT employee_id
FROM employees
WHERE age = @age and service_years = @service_years
When the above code is run, even though both the age and the service_years columns have indexes, they won't be used, and a table scan will be used instead, potentially greatly increasing the amount of time for the query to run.
The reason the indexes are not used is because the Query Analyzer does not know the value of the variables when it selects an access method to perform the query. Because this is a batch file, only one pass is made of the Transact-SQL code, preventing the Query Optimizer from knowing what it needs to know in order to select an access method that uses the indexes.
If you cannot avoid using variables in the WHERE clauses of batch scripts, consider using an INDEX query hint to tell the Query Optimizer to use the available indexes instead of ignoring them and performing a table scan. This of course that the indexes are highly selective. If the indexes are not highly selective, then a table scan most likely be more efficient than using the available indexes. [6.5, 7.0, 2000]

SQL Server Performance Tuning Tips - II

2009-07-22T00:25:00.002+05:30

Continued from SQL server Performance Tunung Tips - I

16. When you have a choice of using a constraint or a trigger to perform the same task, always choose the constraint. The same goes if you have the option of using either a constraint or a rule, or a constraint or a default. Constraints require less overhead than triggers, rules, and defaults. [6.5, 7.0, 2000]

17. Don't implement redundant integrity features in your database. For example, if you are using primary key and foreign key constraints to enforce referential integrity, don't add unnecessary overhead by also adding a trigger that performs the same function. The same goes for using both constraints and defaults or constraints and rules that perform redundant work. While this may sound obvious, it is not uncommon to find in SQL Server databases. [6.5, 7.0, 2000]

18. If you have the choice of using a join or a subquery to perform the same task, generally the join is faster. But this is not always the case, you can may want to test the query using both methods to determine which is faster for your particular application. [6.5, 7.0, 2000]

19. If your application needs to insert a large binary value into an image data column, perform this task using a stored procedure, not using an INSERT statement embedded in your application. The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time). Using a stored procedure avoids all this. [6.5, 7.0, 2000]

20. If you need to create a primary key (using a value meaningless to the record, other than providing a unique value for a record), many developers will use either a identity field (with an integer data type) or a uniqueidentifier data type. If your application is not sensitive to either option, then you will most likely want to choose the identity field over the uniqueidentifier field. The reason for this is that the identity field (using the integer data type) only takes up 4 bytes, while the uniqueidentifier field takes 16 bytes. Using the identifier field will create a smaller and faster index. [7.0, 2000]

21. When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is more efficient and performs faster. [6.5, 7.0, 2000]

22. When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example:
SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)
is much less efficient than this:
SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004
Assuming there is a useful index on customer_number, the Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause (which is really just another form of the OR clause). [6.5, 7.0, 2000]

23. If possible, try to avoid using the SUBSTRING function in your WHERE clauses. Depending on how it is constructed, using the SUBSTRING function can force a table scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a table scan is performed. [6.5, 7.0, 2000]

24. Where possible, avoid string concatenation, as it is not a fast process. [6.5, 7.0, 2000]

25. If possible, try to avoid using data conversion functions in the WHERE clause. If you do, you may be forcing the optimizer to perform a table scan rather than using an applicable index. [6.5, 7.0, 2000]

26. If your application requires you to create temporary tables for use on a global or per connection use, consider the possibility of creating indexes for these temporary tables. While most temporary tables probably won't need, or even can use an index, some larger temporary tables can benefit from them. A properly designed index on a temporary table can be as great a benefit as a properly designed index on a standard database table. [6.5, 7.0, 2000]

27. Generally, you only want to encapsulate your Transact-SQL code in a transaction if it is going to modify the database. Transactions help to ensure database consistency and are an important tool in the developer's toolbox. But if you put non-database modifying Transact-SQL in a transaction, you are producing unnecessary overhead for your application and SQL Server. For example, Transact-SQL used for creating reports doesn't usually need the benefits (and the overhead) associated with transactions. [6.5, 7.0, 2000]

28. Both the MIN() and MAX() functions can take advantage of indexes on columns. So if you perform these functions often, you might want to add an index to the relevant columns, assuming they don't already exit. [6.5, 7.0, 2000]

29. Generally, avoid using optimizer hints in your queries. This is because it is generally very hard to outguess the Query Optimizer. Optimizer hints are special keywords that you include with your query to force how the Query Optimizer runs. If you decide to include a hint in a query, this forces the Query Optimizer to become static, preventing the Query Optimizer from dynamically adapting to the current environment for the given query. More often than not, this hurts, not helps performance.
If you think that a hint might be necessary to optimize your query, be sure you first do all of the following first:
• Update the statistics on the relevant tables.
• If the problem query is inside a stored procedure, recompile it.
• Review the search arguments to see if they are sargable, and if not, try to rewrite them so that they are sargable.
• Review the current indexes, and make changes if necessary.
If you have done all of the above, and the query is not running as you expect, then you may want to consider using an appropriate optimizer hint.
If you haven't heeded my advice and have decided to use some hints, keep in mind that as your data changes, and as the Query Optimizer changes (through service packs and new releases of SQL Server), your hard-coded hints may no longer offer the benefits they once did. So if you use hints, you need to periodically review them to see if they are still performing as expected. [6.5, 7.0, 2000]

30. If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:
• Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
• If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause. [7.0, 2000]

31. While views are often convenient to use, especially for restricting users from seeing data they should not see, they aren't good for performance. So if database performance is your goal, avoid using views (SQL Server 2000 Indexed Views are another story).
Here's why. When the Query Optimizer gets a request to run a view, it runs it just as if you had run the view's SELECT statement from the Query Analyzer. If fact, a view runs slightly slower than the same SELECT statement run from the Query Analyzer--but you probably would not notice the difference--because of the additional overhead caused by the view. Unlike stored procedures, views offer no pre-optimization.
Instead of embedding SELECT statements in a view, put them in a stored procedure instead for optimum performance. Not only do you get the added performance boost, you can also use the stored procedure to restrict user access to table columns they should not see. [6.5, 7.0, 2000]

32. Try to avoid nesting views (referring to a view from within a view). While this is not prohibited, it makes it more difficult to identify the source of any performance problems. A better idea is to create separate views instead of nesting them. [6.5, 7.0, 2000]

33. Don't use DISTINCT or ORDER BY in your SELECT statements unless you really need them. Both options can add a lot of additional overhead to your query, and they aren't always needed for your application. [6.5, 7.0, 2000]

34. If your SELECT statement includes an IN option along with a list of values to be tested in the query, order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list. This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes. [6.5, 7.0, 2000]

35. If you need to use the SELECT INTO option, keep in mind that it can lock system tables, preventing others users from accessing the data they need. If you do need to use SELECT INTO, try to schedule it when your SQL Server is less busy, and try to keep the amount of data inserted to a minimum. [6.5, 7.0, 2000]

Derived Tables in SQL Server

2009-07-22T00:18:00.001+05:30

Derived Tables

Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

SELECT MIN(Salary)
FROM Employees
WHERE EmpID IN
(
SELECT TOP 2 EmpID
FROM Employees
ORDER BY Salary Desc
)

The same query can be re-written using a derived table, as shown below, and it performs twice as fast as the above query:

SELECT MIN(Salary)
FROM
(
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS A

This is just an example, and your results might differ in different scenarios depending on the database design, indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient one.

Benefits of Derived Tables

The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.
Here are the steps when you use a temporary table:
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
4) Release the locks

Compare the above to the number of steps it takes for a derived table:
1) CREATE locks, unless isolation level of "read uncommitted" is used
2) SELECT data (read activity)
3) Release the locks

Using derived tables instead of temporary tables reduces disk I/O and can boost performance. Now let's see how.

Q, How to Run a Query on a Remote SQL Server

A, Use the OpenRowSet Function to Run a Query on a Remote SQL Server
You can use the OPENROWSET( ) function to run a query on a remote SQL server by using the following syntax:

SELECT * FROM OPENROWSET('SQLOLEDB', 'remote_server_name'; 'sa'; 'password','SQL statement')

Here replace 'remote_server_name' with the name of the remote server on which you want to run the query. If necessary, replace 'sa' and 'password' with the name and password of a SQL login ID you want to use to log in to the remote server. Finally, replace 'SQL statement' with the SQL statement you want to run on the remote server

Q, Why does my inline or embedded SQL run faster than my stored procedures?

A, Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:
SELECT * FROM dbo.mytable
This technique helps you reuse plans and prevent cache misses.

Q, How Can I Generate a Series of Random Integers With T-SQL?

Here is a stored procedure that will generate a series of random numbers and return them in a result set.
This SQL Server stored procedure returns a result set that containing a series of random integers.

Inputs are:
@begin_no - lowest number to be generated,
@end_no - highest number to be generated

Calling syntax:
Exec sp_GenerateRandomNumberResultSet Begin, End

CREATE procedure sp_GenerateRandomNumberResultSet
@begin_no int=1, @end_no int=100 As

Set nocount on

Declare @rn int, @rec int, @cnt int
Set @cnt=@end_no-@begin_no+1

Create Table #t (RecNo int identity(1,1), RandNo int null)

Set @rec=1
While @rec<=@cnt
Begin
Insert Into #t (RandNo) Values (null)
Set @rec=@rec+1
End

Set @rec=1
While @rec<=@cnt
Begin
Set @rn=rand()*@cnt+@begin_no
If @begin_no<0 Set @rn=@rn-1
Update #t Set RandNo=@rn
Where RecNo=@rec
And @rn Not In
(Select RandNo
From #t Where RandNo Is Not Null)
If @@rowcount>0 Set @rec=@rec+1
End

Set nocount off

Select RandNo From #t

Drop Table #t
Go

SQL Server 7.0 versions

2009-07-22T00:17:00.000+05:30

Understanding the different SQL Server 7.0 versions

To date, Microsoft has released six different versions of SQL Server 7.0. These versions include the Desktop, Standard, and Enterprise Editions, as well as the Developer, Microsoft Developer (MSDE), and Small Business Server Editions. While all of these versions are SQL Server 7.0, there are some key differences. First of all, you can run the Desktop Edition on Windows NT Workstation 4.0, Windows NT Server 4.0, and Windows 9x. The Desktop Edition doesn't support the Microsoft Search Service, OLAP Services, parallel queries, or transaction replication--and it can't be bought on its own. Instead, you must buy either the Standard or the Enterprise Edition to get the Desktop Edition. You can run the Standard Edition of SQL Server only on Windows NT Server 4.0 (or later). This version does support such features as the Microsoft Search Service, OLAP Services, parallel queries, and transactional replication. It also supports up to 4 CPUs and 2 GB of RAM. In contrast, the Enterprise Edition runs only on Windows NT Server 4.0 Enterprise Edition and supports Microsoft Cluster Server. It also supports all of the features supported in the Standard Edition--plus up to 32 CPUs and more than 2 GB of RAM. The Developer Edition of SQL Server is included with Visual Studio for developer use. This version supports a limited number of connections but does include debugging tools. The Microsoft Developer Edition (MSDE) of SQL Server is simply a run-time engine that's included as part of Microsoft Office 2000. Although the MSDE version includes some of the management utilities, it doesn't include all of them. The MSDE was designed for you to distribute as part of an application, not as a stand-alone product. Finally, the Small Business Server Edition is part of Microsoft's Small Business Server. This version of SQL Server is essentially the same as the Standard Edition but comes hard-coded with a limit of 100 users and a maximum database size of 10 GB.

Except operator in SQL Server

2009-07-22T00:15:00.001+05:30

Getting the last identity value used

Many times, you'll want to know the last identity (key) value that was used in an insert. The biggest reason for this is so that the same value can be reused when inserting a foreign key. This is done differently between SQL Server and DB2. In DB2, the identity values can be picked up by the application and reused using and Identity_Val_function(), which returns the most recently assigned value for an identity column. In SQL Server, the last identity value used in an insert can be retrieved with the @@identity function.

Except Operator

To find rows in one set that do not exist in another set, use the except operator (as defined in SQL-92 and SQL-99). For example, here's how you find column1 from Table1 that does not exist in column2 of Table2:
Select column1 from Table1 Except Select column2 from Table2; The except operator will remove duplicates, and a single null value will be returned in the case of multiple null values. To return duplicates, use except all. Keep in mind, of course, that other proprietary implementations (such as Minus in Oracle) exist.

Who Is Blocking Your SQL Server

2009-07-22T00:13:00.001+05:30

See Who Is Blocking Your SQL Server

If you have ever monitored any blocking problems in SQL Server, you know that sp_who only shows you the spid (SQL Server's internal Process ID) that is causing the blocking for each spid that is blocked. Often a blocked spid is shown as causing blocking for another spid. To see the spid (or spids) that started the whole mess off, execute the following SQL:

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.suid = l.suid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )

We built this into our own version of sp_who, called sp_hywho. See the listing below. Code for sp_hywho:

if exists (select * from sysobjects
where id = object_id('dbo.sp_hywho')
and sysstat & 0xf = 4)
drop procedure dbo.sp_hywho
GO

Create Procedure sp_hywho
( @vcDBName sysname = NULL )
AS

SET NOCOUNT ON

IF EXISTS ( SELECT 1
FROM master..sysprocesses p
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid ) )
BEGIN
PRINT "Blocking caused by:"
PRINT ""
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.suid = l.suid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )
AND (p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5
PRINT ""
END

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.suid = l.suid
WHERE (p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5

SET NOCOUNT OFF

GO

if exists (select * from sysobjects
where id = object_id('dbo.sp_hywho')
and sysstat & 0xf = 4)
GRANT EXEC ON dbo.sp_hywho TO PUBLIC
GO

Joining Queries from Oracle and SQL Server Databases

2009-07-22T00:10:00.002+05:30

Question: I need a query that retrieves info from an Oracle table and a query that retrieves info from a SQL Server table. The info has to be joined together according to Record ID numbers. I have very limited access to the Oracle database but full control of the SQL Server database.How do I join two different queries from two different databases?

Answer: To query to different data sources, you can make the Oracle server a linked server to the SQL Server server. A linked server can be any OLE DB data source and SQL Server currently supports the OLE DB data provider for Oracle. You can add a linked server by calling sp_AddLinkedServer and query information about linked servers with sp_LinkedServers.

An easier way to add a linked server is to use Enterprise Manager. Add the server through the Linked Servers icon in the Security node. Once a server is linked, you can query it using a distributed query (you have to specify the full name).

Here's an example of a distributed query (from the SQL Server Books Online) that queries the Employees table in SQL Server and the Orders table from Oracle:

SELECT emp.EmloyeeID, ord.OrderID, ord.Discount
FROM SQLServer1.Northwind.dbo.Employees AS emp,
OracleSvr.Catalog1.SchemaX.Orders AS ord
WHERE ord.EmployeeID = emp.EmployeeID
AND ord.Discount > 0

Use the OpenRowSet Function to Run a Query on a Remote SQL Server

You can use the OPENROWSET( ) function to run a query on a remote SQL server by using the following syntax:

SELECT *FROM OPENROWSET('SQLOLEDB', 'remote_server_name'; 'sa'; 'password','SQL statement')
Here replace 'remote_server_name' with the name of the remote server on which you want to run the query. If necessary, replace 'sa' and 'password' with the name and password of a SQL login ID you want to use to log in to the remote server. Finally, replace 'SQL statement' with the SQL statement you want to run on the remote server

Check Whether a Global Temporary Exists in a SQL Database

Checking whether a table exists in a Microsoft SQL Server database is easy. You can use this query:

SELECT 'x'
FROM sysobjects
WHERE type = 'U' and NAME = 'mytable'
But this query will not work while searching for global temporary tables. Global temporary tables are stored in tempdb.
Use this syntax for the search:

DECLARE @temp_table VARCHAR(100)
SET @temp_table = '##my_temp_table'
IF NOT EXISTS (SELECT 'x'
FROM tempdb..sysobjects
WHERE type = 'U' and NAME = @temp_table)
PRINT 'temp table ' + @temp_table + ' does not exist'

ELSE
PRINT 'temp table ' + @temp_table + ' exists.'
Note: You cannot search for local temporary tables (# prefix tables) in this way. This is because SQL Server appends a unique number to the name you supply. For example, if you specified "#temp," the name in sysobjects would be something like "#temp____1234."

SQL Server Performance Tuning Tips - I

2009-07-22T00:09:00.004+05:30

Transact-SQL SQL Server Performance Tuning Tips

1. Slow queries can be caused by a wide variety of reasons. Some include:
• Lack of useful indexes
• Lack of useful I/O striping
• Out-of-date statistics or lack of useful statistics
• Lack of physical memory
• Slow network connection
• Transact-SQL queries transferring large amounts of data from the server to the client
• Blocked locks or deadlocks
• Performing OLTP and OLAP queries on the same physical server
• Poorly designed query
Any one or more of the above items can cause slow query performance. Each of these problem areas, and more, are discussed in detail on this page and other pages on this website.

2. This tip may sound obvious to most of you, but I have seen professional developers, in two major SQL Server-based applications used worldwide, not follow it. And that is to always include a WHERE clause in your SELECT statement to narrow the number of rows returned. If you don't use a WHERE clause, then SQL Server will perform a table scan of your table and return all of the rows. In some case you may want to return all rows, and not using a WHERE clause is appropriate in this case. But if you don't need all the rows returned, use a WHERE clause to limit the number of rows returned.

3. Don't be afraid to make liberal use of in-line and block comments in your Transact-SQL code, they will not affect the performance of your application.

4. If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. Some options are to perform the task at the client, use tempdb tables, use derived tables, use a correlated sub-query, or use the CASE statement. More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor.

5. To help identify long running queries, use the SQL Server Profiler Create Trace Wizard to run the "TSQL By Duration" trace. You can specify the length of the long running queries you are trying to identify (such as over 1000 milliseconds), and then have these recorded in a log for you to investigate later.

6. When using the UNION statement, keep in mind that by default it performs the equivalent of a SELECT DISTINCT on the results set. If you know that there will not be any duplication of rows created as a result of using the UNION statement, then use the UNION ALL statement instead. This variation of the statement does not look for duplicate rows and runs must faster than the UNION, which does look for duplicate rows, whether or not there are any.

7. Carefully evaluate whether your query needs the DISTINCT clause or not. The DISTINCT clause slows down virtually every query it is in. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary. This is a bad habit that should be stopped. In addition, keep in mind that in some cases, duplicate results in a query are not a problem. If this is the case, then don't use a DISTINCT clause.

8. In your queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. [6.5, 7.0, 2000]

9. In your queries, don't return rows you don't need. Use the WHERE clause to return only those rows you need right now. [6.5, 7.0, 2000]

10. If your users perform many ad hoc queries on your SQL Server data, and you find that many of these "poorly-written" queries take up an excessive amount of SQL Server resources, consider using the "query governor cost limit" configuration option to limit how long a query can run. You can specify the maximum amount of "seconds" a query will run, and whenever the query optimizer determines that a particular query will exceed the maximum limit, the query will be aborted before it even begins. Although the value you set for this setting is stated as "seconds", it does not mean seconds like we think of seconds. Instead, it relates to the actual estimated cost of the query as calculated by the query optimizer. You may have to experiment with this value until you find one that meets your needs.
There are two ways to set this option. First, you can change it at the server level (all queries running on the server are affected by it) using sp_configure "query governor cost limit", or you can set it at the connection level (only this connection is affected) by using the SET QUERY_GOVERNOR_COST_LIMIT command. [7.0, 2000]

11. If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data they don't need, consider using the TOP operator within the SELECT statement. This way, you can limit how may rows are returned, even if the user doesn't enter any criteria to help reduce the number or rows returned to the client. For example, the statement:
SELECT TOP 100 fname, lname FROM customers
WHERE state = 'mo'
limits the results to the first 100 rows returned, even if 10,000 rows actually meet the criteria of the WHERE clause. When the specified number of rows is reached, all processing on the query stops, potentially saving SQL Server overhead, and boosting performance.
The TOP operator works by specifying a specific number of rows, like the example above, or by specifying a percentage value, like this:
SELECT TOP 10 PERCENT fname, lname FROM customers
WHERE state = 'mo'
In the above example, only 10 percent of the available rows would be returned. [7.0, 2000]

12. You may have heard of a SET command called SET ROWCOUNT. Like the TOP operator, it is designed to limit how many rows are returned from a SELECT statement. In effect, the SET ROWCOUNT and the TOP operator perform the same function. While is some cases, using either option works equally efficiently, there are some instances (such as rows returned from an unsorted heap) where the TOP operator is more efficient than using SET ROWCOUNT. Because of this, using the TOP operator is preferable to using SET ROWCOUNT to limit the number of rows returned by a query. [7.0, 2000]

13. Try to avoid WHERE clauses that are non-sargable. Non-sargable search arguments in the WHERE clause, such as "IS NULL", "OR", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE %500" can prevent the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, or expressions that have the same column on both sides of the operator, are not sargable.
But not every WHERE clause that has a non-sargable expression in it is doomed to a table scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use an index (if one exists) to help access the data quickly. [6.5, 7.0, 2000]

14. If many queries are run off very large tables, consider breaking down the tables into one or more logical subsets using views. This works best for data that is easily partitioned, such as data based on date or location. This way, users can query off the appropriate view instead of the base table, saving a lot of I/O overhead. [6.5, 7.0, 2000]

15. If you application needs to retrieve summary data often, but you don't want to have the overhead of calculating it on the fly when it is needed, consider using a trigger that updates summary values after the initial transaction completes. While the trigger has some overhead, overall, it may be less that having to calculate the data every time the summary data is needed. You will have to decide which method is fastest in your environment. [6.5, 7.0, 2000]

STORED PROCEDURES IN SQL SERVER

2009-07-22T00:04:00.003+05:30

Pros and Cons of Extended Stored Procedures

Question: What are the pros and cons of creating extended stored procedures?

Answer: The pros are that you can implement additional functionality and access data from DLLs from within SQL Server. If you need to do something that can be done only in C or C++, or if you have data that can be accessed only outside of SQL Server, you can still provide a link to it. The biggest con to extended stored procedures is that they run in the same process space as SQL Server. So an errant DLL could overwrite memory and cause SQL Server to crash or even corrupt data. The biggest safeguard against these problems is thorough testing of the procedure

Percent Sign in SQL

Question: I have a table in which the key field has a value stored with a percent sign, like '1234%'. Using this value, I want to select from another table that can have values like '1234567', '1234678' and '1234098'. How do I go about it?

Answer: The percent sign (%) is a wildcard in SQL Server. It can be used at the beginning or end of a string. So the following syntax will return all of the records you mentioned:

SELECT * FROM TestTable WHERE Col LIKE '1234%'

Col
-------
1234567
1234678
1234098
If you want to do an exact match for '1234' without the percent sign, then you'll have to trim off the last character, like this:
SELECT * FROM TestTable WHERE Col LIKE LEFT('1234%', (LEN('1234%')-1))

Delete Duplicate Rows with a Single SQL Statement

Question: Is it possible to delete duplicate rows in a table without using a temporary table (i.e., just do it with a single SQL statement)?

Answer: All you need to do is compare the table to itself to find out which candidates are duplicates. Do this by assigning aliases to the table so you can use it twice, once as A and again as B, like this:

delete
from jobs
where
job_desc in
(
select
a.job_desc
from
jobs a,
jobs b
where
a.job_desc = b.job_desc
group by
a.job_desc
having
count(a.job_desc) >1
)

When you do this you'll get a count based on the column value you think is duplicated. I used "desc" because the IDs will be different, so the description is the thing that is the candidate for repetition. Join the table to itself on that candidate to find matches of it. Everything will match to itself at least once that's why you group by the thing you think is a duplicate. Applying the HAVING clause to it squeezes out all the "ones" or singletons, leaving only the rows that have counts that are more than one in other words, your duplicate rows.

By the way, this code trashes all the records that are duplicates. If you want to save one, add a comparison for the IDs to be different in the WHERE clause.

SQL SERVER PERFORMANCE TUNING TIPS

2009-07-22T00:02:00.003+05:30

Miscellaneous SQL Server Performance Tuning Tips

1. If you need to delete all the rows in a table, don't use DELETE to delete them all, as the DELETE statement is a logged operation and can take time. To perform the same task much faster, use the TRUNCATE TABLE instead, which is not a logged operation. Besides deleting all of the records in a table, this command will also reset the seed of any IDENTITY column back to its original value. [6.5, 7.0, 2000] Updated 7-3-2001

2. Don't run a screensaver on your production SQL Server, it can unnecessarily use CPU cycles that should be going to your application. The only exception to this is the "blank screen" screensaver, which is OK to use. [6.5, 7.0, 2000]

3. Use sp_who and sp_who2 (sp_who2 is not documented in the SQL Server Books Online, but offers more details than sp_who) to provide locking and performance-related information about current connections to SQL Server. [6.5, 7.0, 2000]

4. By default, you cannot use a UNC (Universal Naming Convention) name to specify a location of where to store a SQL Server database or log file. Instead, you must specify a drive letter that refers to a local physical drive or array. But what if you want to store your database or log file on another NT Server or a Network Appliance, Inc. storage system? You can, but you will have to set Trace Flag 1807 on your SQL Server to allow the use of UNC names. [7.0]

5. For a quick and dirty way to check to see if your SQL Server has maxed out its memory (and causing your server to page), try this. Bring up the Task Manager and go to the "Performance" tab.
Here, check out two numbers: the "Total" under "Commit Charge (k)" and the "Total" under "Physical Memory (k)". If the "Total" under "Commit Charge (k)" is greater than the "Total" under "Physical Memory (k)", then your server does not have enough physical memory to run efficiently as it is currently configured and is most likely causing your server to page unnecessarily. Excess paging will slow down your server's performance.
If you notice this problem, you will probably want to use the Performance Monitor to further investigate the cause of this problem. You will also want to check to see how much physical memory has been allocated to SQL Server. Most likely, this setting has been set incorrectly, and SQL Server has been set to use too much physical memory. Ideally, SQL Server should be set to allocate physical RAM dynamically. [6.5, 7.0, 2000]

6. Internet Information Server (IIS) has the ability to send its log files directly to SQL Server for storage. Busy IIS servers can actually get bogged down trying to write log information directly to SQL Server, and because of this, it is generally not recommend to write a web logging information to SQL Server. Instead, logs should be written to text files, and later imported into SQL Server using BCP or DTS. [6.5, 7.0, 2000]

7. SQL Server 7 has a database compatibility mode that allows applications written for previous versions of SQL Server to run under SQL Server 7. In you want maximum performance for your database, you don't want to run your database in compatibility mode. Instead, it should be running in native SQL Server 7 mode. Of course, this may require you to modify your application to make it SQL Server 7 compliant, but in most cases, the additional work required to update your application will be more than paid for with improved performance. [7.0, 2000]

8. When experimenting with the tuning of your SQL Server, you may want to run the DBCC DROPCLEANBUFFERS command to remove all the test data from SQL Server's data cache (buffer) between tests to ensure fair testing. If you want to clear out the stored procedure cache, use this command, DBCC FREEPROCCACHE. Both of these commands are for testing purposes and should not be run on a production SQL Server. [7.0, 2000]

9. Orphan SQL Server sessions can negatively affect SQL Server's performance. An orphan SQL Server session can occur when a client improperly disconnects from SQL Server, such as when the client looses power. When this happens, the client cannot tell SQL Server to properly close the connection, so the SQL Server connection remains open, even though it is not being used. This can affect SQL Server's performance two ways. First, they use up SQL Server connections, which takes up server resources. Secondly, it is possible that the orphan connections may be holding locks that block other users, or temp tables or cursors may be held open that also take up unnecessary server resources. NT Server periodically checks for inactive SQL Server sessions, and if it finds any, it will notify SQL Server so that the connection can be removed. Unfortunately, NT Server only performs this check every 1-2 hours, depending on the protocol used. If orphaned SQL Server sessions become a problem, NT Server's registry can be modified so that it checks more often for orphaned connections.
Identifying an orphaned connection from SQL Server is very difficult, but if you can identify it, it can be removed by KILLing it using Enterprise Manager or Query Analyzer. [6.5, 7.0, 2000] Added 10-2-2000

10. If your application opens a DB-Library connection to SQL Server 7.0 and it uses either the TCP/IP or IPX/SPX network libraries, and you are using SQL Server 7.0 with no service pack or Service Pack 1, then SQL Server will experience severe memory leaks each time a new connection is opened, that can significantly affect its performance. To resolve this problem, the best solution is to install Service Pack 2, which will correct the problem. If you cannot install the service pack, then another option is to use another network library, such as Named Pipes or Multiprotocol. [7.0] More from Microsoft Added 11-15-2000

11. For best performance, don't mix production databases and development (test or staging) databases on the same physical server. This not only serves to better separate the two functions (production and development), but prevents developers from using up server resources that could be better used by production users. [6.5, 7.0, 2000] Added 11-20-2000

12. When we think of performance, we usually think about speeding up our application's performance. But another way to look at performance is to look at our performance as DBA's or Transact-SQL developers.
For example, one of the easiest ways to speed up our Transact-SQL coding, in addition to maintaining and troubleshooting our code once it is written, it to format it in an easy to read format.
While there are many different code formatting guidelines available, here are some basic ones you should consider following, if you aren't doing so already:
• Begin each line of your Transact-SQL code with a SQL verb, and capitalize all Transact-SQL statements and clauses, such as:
SELECT customer_number, customer_name
FROM customer
WHERE customer_number > 1000
ORDER BY customer_number
• If a line of Transact-SQL code is too long to fit onto one line, indent the following line(s), such as:
SELECT customer_number, customer_name, customer_address,
customer_state, customer_zip, customer_phonenumber
• Separate logical groupings of Transact-SQL code by using appropriate comments and documentation explaining what each grouping goes.
These are just a few of the many possible guidelines you can follow when writing your Transact-SQL code to make it more readable by you and others. You just need to decide on some standard, and then always follow it in your coding. If you do this, you will definitely boost your coding performance. [6.5, 7.0, 2000] Added 12-5-2000

13. Be wary of allowing users to directly access your databases (especially OLTP databases) with third-party database access tools, such as Microsoft Excel or Access. Many of these tools can wreck havoc with your database's performance. Here are some reasons why:
• Often these users aren't experienced using these tools, and create overly complex queries that eat up server resources. At the other extreme, their queries may not be complex enough (such as lacking effective WHERE clauses) and return thousands, if not millions, or unnecessary rows of data.
• This reporting activity can often lock rows, pages, or tables, creating user contention for data and reducing the database's performance.
• These tools are often file-based. This means that even if an effective query is written, the entire table (or multiple tables in the case of joins) have to be returned to the client software where the query is actually performed, not at the server. This can not only lead to excess server activity, but it can play havoc on your network.
If you have no choice but to allow users access to your data, try to avoid them hitting your production OLTP databases. Instead, point them to a "reporting" server that has been replicated, or is in the form of a datamart or data warehouse. [6.5, 7.0, 2000] Added 4-18-2001

14. SQL Server 2000 offers support of SSL encryption between clients and the server. While selecting this option prevents the data from being viewed, it also adds additional overhead and reduces performance. Only use SSL encryption if absolutely required. If you need to use SSL encryption, consider purchasing a SSL encryption processor for the server to speed performance. [2000] Added 9-21-2000

15.

www.sureshkumar.net
16.
17. SQL Server 2000 supports named instances of SQL Server. For example, this feature allows you to run both SQL Server 6.5 and SQL Server 2000 on the same server; or to run SQL Server 7.0 and SQL Server 2000 on the same server; or to run SQL Server 6.5, SQL Server 7.0, and SQL Server 2000 on the same server; and to run up to 16 concurrent instances of SQL Server 2000 on the same server.
As you might imagine, each running instance of SQL Server takes up server resources. Although some resources are shared by multiple running instances, such as MSDTC and the Microsoft Search services, most are not. Because of this, each additional instance of SQL Server running on the same server have to fight for available resources, hurting performance. For best performance, run only a single instance (usually the default) on a single physical server. The main reasons for using named instances is for upgrading older versions of SQL Server to SQL Server 2000, transition periods where you need to test your applications on multiple versions of SQL Server, and for use on developer's workstations. [2000] Added 11-14-2000

18. If you run the ALTER TABLE DROP COLUMN statement to drop a variable length or text column, did you know that SQL Server will not automatically reclaim this space after performing this action. To reclaim this space, which will help to reduce unnecessary I/O due to the wasted space, you can run the following command, which is new to SQL Server 2000.
DBCC CLEANTABLE (database_name, table_name)
Before running this command, you will want to read about it in Books Online to learn about some of its options that may be important to you. [2000] Added 2-28-2001

19. Trace flags, which are used to enable and disable some special database functions temporarily, can often chew up CPU utilization and other resources on your SQL Server unnecessarily. If you just use them for a short time to help diagnose a problem, for example, and then turn them off as soon as your are done using them, then the performance hit you experience is small and temporary.
What happens sometimes is that you, or another DBA, turns on a trace flag, but forgets to turn it off. This of course, can negatively affect your SQL Server's performance. If you want to check to see if there are any trace flags turned on on a SQL Server, run this command in Query Analyzer:
DBCC TRACESTATUS(-1)
If there are any trace flags on, you will see them listed on the screen after running this command. DBCC TRACESTATUS only finds traces created at the client (connection) level. If a trace has been turned on for an entire server, this will not show up.
If you find any, you can turn them off using this command:
DBCC TRACEOFF(number of trace)
[7.0, 2000] Added 6-6-2001

20. SQL Server offers a feature called the black box. When enabled, the black box creates a trace file of the last 128K worth of queries and exception errors. This can be a great tool for troubleshooting some SQL Server problems, such as crashes.
Unfortunately, this feature uses up SQL Server resources to maintain the trace file than can negatively affect its performance. Generally, you will want to only turn the black box on when troubleshooting, and turn it off during normal production. This way, your SQL Server will be minimally affected. [7.0, 2000] Added 6-6-2001

21. If you have ever performed a SELECT COUNT(*) on a very large table, you know how long it can take. For example, when I ran the following command on a large table I manage:
SELECT COUNT(*) from
It took 1:09 to count 10,725,948 rows in the table. At the same time, SQL Server had to perform a lot of logical and physical I/O in order to perform the count, chewing up important SQL Server resources.
A much faster, and more efficient, way of counting rows in a table is to run the following query:
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('') AND indid < 2
When I run the query against the same table, it takes less than a second to run, and it gave me the same results. Not a bad improvement, and it took virtually no I/O. This is because the row count of your tables is stored in the sysindexes system table of your database. So instead of counting rows when you need to, just look up the row count in the sysindexes table.
The is one potential downside to using the sysindexes table. And that this system table is not updated in real time, so it might underestimate the number of rows you actually have. Assuming you have the database option turned on to "Auto Create Statistics" and "Auto Update Statistics", the value you get should be very close to being correct, if not correct. If you can live with a very close estimate, then this is the best way to count rows in your tables. [7.0, 2000] Added 7-3-2001

22. Looking for some new tools to help performance tune your operating system? Then check out the performance tools at Sysinternals. For example, they have tools to defrag your server's swap file, among many others. And best of all, most are free. [6.5, 7.0, 2000] Added 7-3-2001

23. Do you use Enterprise Manager to access remote servers, possibly over a slow WAN link? If you do, have you ever had any problems getting Enterprise Manager to connect to the remote server?
If so, the problem may lay in the fact that if Enterprise Manager cannot make a connection within 4 seconds, then the connection attempt fails. To overcome slow network connections, you can change the default Enterprise Manager timeout value from 4 seconds to any amount of time you like.
To change the default timeout value, select Tools|Options from Enterprise Manager, and then select the "Connection" tab if you have SQL Server 7.0, or the "Advanced" tab if you have SQL Server 2000. Here, change the "Login time-out (seconds)" option to a higher number. [7.0, 2000] Added 7-3-2001

24. SQLDIAG.exe is a command line tools that collects information about SQL Server and writes it to a text file. It can be useful for documenting or troubleshooting your SQL Server. When you run this command when SQL Server is running, the following information is collected and stored in a text file called sqldiag.txt, which is stored in the \mssql\log folder.
• The text of all error logs
• SQL Server registry information
• SQL Server dll version information
• The output from these system stored procedures:
• sp_configure
• sp_who
• sp_lock
• sp_helpdb
• xp_msver
• sp_helpextendedproc
• sysprocesses
• Input buffer SPIDs/deadlock information
• Microsoft diagnostics report for the server
• The last 100 queries and exceptions (if the query history trace was running)
[ 7.0, 2000] Added 10-12-2001

25. If you are running any service pack of SQL Server 7.0 on a multiprocessor server, the number of configured processors displayed by Enterprise Manager my be incorrect. For example, while you may have two CPUs in your server, Enterprise Manager may only report one CPU. This is a known bug that has yet to be fixed in Service Packs 1 through 3 in SQL Server 7.0.
The work-around to this problem is not to use the Enterprise Manager to specify the number of CPUs for SQL Server to use, but to use the sp_configure "max degree of parallelism" option instead, such as in this example:
USE master
GO

SP_CONFIGURE 'show advanced option', 1
GO

RECONFIGURE WITH OVERRIDE
GO

SP_CONFIGURE 'max degree of parallelism', 0
GO
The "max degree of parallelism" option is an advanced option, so the first portion of the code above is used to turn on the "show advanced option." Once that is done, then you can set the "max degree of parallelism" option. By setting this option to "0", you are telling SQL Server to use all available CPUs in the server. See this Microsoft article for more information: http://support.microsoft.com/support/kb/articles/Q273/8/80.ASP [7.0] Added 10-23-2001

26. Memory leaks can steal valuable memory from your SQL Server, reducing performance, and perhaps even forcing you to reboot your server. A memory leak occurs when a poorly-written or buggy program requests memory from the operating system, but does not release the memory when it is done with it. Because of this, the application can use up more and more memory in a server, greatly slowing it down, and even perhaps crashing the server.
Some memory leaks come from the operating system itself, device drivers, MDAC components, and even SQL Server. And of course, virtually any application can cause a memory leak, which is another good reason to dedicate a single server to SQL Server instead of sharing it among multiple applications.
Memory leaks are often hard to identify, especially if they leak memory slowly. Generally, memory leaks become apparent when you notice that your server is running out of available memory and paging becomes a big problem. A symptom of this is a SQL Server that runs quickly after being rebooted, but begins to run more and more slowly as time passes, and when the system is rebooted again, it speeds up again.
One way to help get rid of many memory leaks is to ensure that you always have the latest service packs or updates for your server's software. But a memory leak you find may not have an immediate fix. If this is the case, you may be forced to reboot your server periodically in order to free up memory.
Identifying what is causing a memory leak is often difficult. One method involved using Performance Monitor to monitor all of the counters in the Memory object over time, seeing what is happening internally in your computer. Another method is to use Task Manager to view how much memory is used by each process. A process that seems to be using an unusual amount of memory may be the culprit. [6.5, 7.0, 2000]

Optimizing SQL Server Indexes

2009-07-21T23:57:00.004+05:30

Tips on Optimizing SQL Server Indexes

1. Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses. Without an index, each of these operations will require a table scan of your table, potentially hurting performance.

Keep in mind the word "considered". An index created to support the speed of a particular query may not be the best index for another query on the same table. Sometimes you have to balance indexes to attain acceptable performance on all the various queries that are run against a table. [6.5, 7.0, 2000] Updated 12-7-2001

2. To help identify which tables in your database may need additional or improved indexes, use the SQL Server Profiler Create Trace Wizard to run the "Identify Scans of Large Tables" trace. This trace will tell which tables are being scanned by queries instead of using an index to seek the data. This should provide you data you can use to help you identify which tables may need additional or better indexes. [7.0, 2000]

3. Don't over index your OLTP tables, as every index you add increases the time in takes to perform INSERTS, UPDATES, and DELETES. There must be a fine line drawn between having the ideal number of indexes (for SELECTs) and the ideal number for data modifications. [6.5, 7.0, 2000]

4. Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table. [6.5, 7.0, 2000]

5. Don't accidentally add the same index twice on a table. This is easier to happen than you think. For example, you add a unique or primary key to an column, which of course creates an index to enforce what you want to happen. But without thinking about it when evaluating the need for indexes on a table, you decide to add a new index, and this new index happens to be on the same column as the unique or primary key. As long as you give indexes different names, SQL Server will allow you to create the same index over and over. [7.0, 2000] Added 4-2-2001

6. If you have a table that is subject to many INSERTS, be sure that you have added a clustered index to it (not based on an incrementing key), whether or not it really needs one. A table that does not have a clustered index is called a heap. Every time data is INSERTed into a heap, the row is added to the end of the table. If there are many INSERTS, this spot could become a "hot spot" which could significantly affect performance. By adding a clustered index to the table (not based on an incrementing key), any potential hotspots are avoided. [6.5, 7.0, 2000] Added 2-22-2001

7. Drop indexes that are never used by the Query Optimizer. Unused indexes slow data modifications and waste space in your database, increasing the amount of time it takes to backup and restore databases. Use the Index Wizard ( 7.0 and 2000) to help identify indexes that are not being used. [6.5,
8. Generally, you probably won't want to add an index to a table under these conditions:
• If the index is not used by the query optimizer. Use Query Analyzer's "Show Execution Plan" option to see if your queries against a particular table use an index or not. If the table is small, most likely indexes will not be used.
• If the column values exhibit low selectivity, often less than 90%-95% for non-clustered indexes.
• If the column(s) to be indexed are very wide.
• If the column(s) are defined as TEXT, NTEXT, or IMAGE data types.
• If the table is rarely queried.

9. While high index selectivity is generally an important factor that the Query Optimizer uses to determine whether or not to use an index, there is one special case where indexes with low selectivity can be useful speeding up SQL Server. This is the case for indexes on foreign keys. Whether an index on a foreign key has either high or low selectivity, an index on a foreign key can be used by the Query Optimizer to perform a merge join on the tables in question. A merge join occurs when a row from each table is taken and then they are compared to see if they match the specified join criteria. If the tables being joined have the appropriate indexes (no matter the selectivity), a merge join can be performed, which is generally much faster than a join to a table with a foreign key that does not have an index. [7.0, 2000] Added 4-9-2001

10. On data warehousing databases, which are essentially read-only, having an many indexes as necessary for covering virtually any query is not normally a problem. [6.5, 7.0, 2000]

11. To provide the up-to-date statistics the query optimizer needs to make smart query optimization decisions, you will generally want to leave the "Auto Update Statistics" database option on. This helps to ensure that the optimizer statistics are valid, helping to ensure that queries are properly optimized when they are run.
But this option is not a panacea. When a SQL Server database is under very heavy load, sometimes the auto update statistics feature can update the statistics at inappropriate times, such as the busiest time of the day.
If you find that the auto update statistics feature is running at inappropriate times, you may want to turn it off, and then manually update the statistics (using UPDATE STATISTICS or sp_updatestats) when the database is under a less heavy load.
But again, consider what will happen if you do turn off the auto update statistics feature? While turning this feature off may reduce some stress on your server by not running at inappropriate times of the day, it could also cause some of your queries not to be properly optimized, which could also put extra stress on your server during busy times.
Like many optimization issues, you will probably need to experiment to see if turning this option on or off is more effective for your environment. But as a rule of thumb, if your server is not maxed out, then leaving this option is probably the best decision. [7.0, 2000] More info from Microsoft

12. Keep the "width" of your indexes as narrow as possible, especially when creating composite (multi-column) indexes. This reduces the size of the index and reduces the number of reads required to read the index, boosting performance. [6.5, 7.0, 2000]

13. If possible, try to create indexes on columns that have integer values instead of characters. Integer values have less overhead than character values. [6.5, 7.0, 2000]

14. An index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000]

15. Even if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index. [6.5, 7.0, 2000]

16. When you create an index with a composite key, the order of the columns in the key is important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key. If you don't due this, and put a non-selective column at the first part of the key, you risk having the Query Optimizer not use the index at all. Generally, a column should be at least 95% unique in order for it to be considered selective. [6.5, 7.0, 2000] Updated 3-6-2001

17. As you may know, an index is automatically created for column(s) in your table that you specify as a PRIMARY KEY or as UNIQUE. If two or more columns are involved, and a composite index is created, you should choose how the columns will be ordered in the composite index, instead of accepting the default choices offered by SQL Server. This is because you always want to use the most selective columns at the left of the key to ensure that the composite index is selective enough to be used by the Query Optimizer. If you don't do this, then the default order of the columns in the composite index may not be very selective, and the index may not be used by Query Optimizer. While the order of the columns in the index are important to the Query Optimizer, the order is not important to the PRIMARY KEY or UNIQUE constraints. [6.5, 7.0, 2000] Added 3-6-2001

18. If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index. If the columns used for the joins are not naturally compact, then considering adding surrogate keys to the tables that are compact in order to reduce the size of the keys, thus decreasing I/O during the join process, increasing overall performance. [6.5, 7.0, 2000]

19. When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once it is found, SQL Server doesn't have to look any further. [6.5, 7.0, 2000] Added 10-19-2000

20. If a particular query against a table is run infrequently, and the addition of an index greatly speeds the performance of the query, but the performance of INSERTS, UPDATES, and DELETES is negatively affected by the addition of the index, consider creating the index for the table for the duration of when the query is run, then dropping the index. An example of this is when monthly reports are run at the end of the month on an OLTP application. [6.5, 7.0, 2000] Added 9-11-2000

21. If you like to get under the cover of SQL Server to learn more about indexing, take a look at the sysindex system table that is found in every database. Here, you can find a wealth of information on the indexes and tables in your database. To view the data in this table, run this query from the database you are interested in: SELECT *
FROM sysindexes. Here are some of the more interesting fields found in this table:
• dpages: If the indid value is 0 or 1, then dpages is the count of the data pages used for the index. If the indid is 255, then dpages equals zero. In all other cases, dpages is the count of the non-clustered index pages used in the index.
• id: Refers to the id of the table this index belongs to.
• indid: This column indicates the type of index. For example, 1 is for a clustered table, a value greater than 1 is for a non-clustered index, and a 255 indicates that the table has text or image data.
• OrigFillFactor: This is the original fillfactor used when the index was first created, but it is not maintained over time.
• statversion: Tracks the number of times that statistics have been updated.
• status: 2 = unique index, 16 = clustered index, 64 = index allows duplicate rows, 2048 = the index is used to enforce the Primary Key constraint, 4096 = the index is used to enforce the Unique constraint. These values are additive, and the value you see in this column may be a sum of two or more of these options.
• used: If the indid value is 0 or 1, then used is the number of total pages used for all index and table data. If indid is 255, used is the number of pages for text or image data. In all other cases, used is the number of pages in the index.

22. Don't use FLOAT or REAL data types for primary keys, as they add unnecessary overhead and can hurt performance. [6.5, 7.0, 2000] Added 10-4-2000

23. If your WHERE clause includes an AND operator, one way to optimize it is to ensure that at least one of the search criterion is highly selective and includes an index for the relevant column. [6.5, 7.0, 2000] Added 10-17-2000

24. The Query Optimizer will always perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or does not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column has an index. [7.0, 2000] Added 10-17-2000

25. The Query Optimizer converts the Transact-SQL IN clause to the OR operator when parsing your code. Because of this, keep in mind that if the referenced column in your query doesn't include an index, then the Query Optimizer will perform a table scan or clustered index scan on the table. [6.5, 7.0, 2000] Added 10-17-2000

26. If you use the SOUNDEX function against a table column in a WHERE clause, the Query Optimizer will ignore any available indexes and perform a table scan. If your table is large, this can present a major performance problem. If you need to perform SOUNDEX type searches, one way around this problem is to precalculate the SOUNDEX code for the column you are searching and then place this value in a column of its own, and then place an index on this column in order to speed searches. [6.5, 7.0, 2000] Added 11-9-2000

27. If you need to create indexes on large tables, you may be able to speed up their creation by using the new SORT_IN_TEMPDB option available with the CREATE INDEX command. This option tells SQL Server to use the tempdb database, instead of the current database, to sort data while creating indexes. Assuming your tempdb database is isolated on its own separate disk or disk array, then the process of creating the index can be sped up. The only slight downside to using this option is that it takes up slightly more disk space than if you didn't use it, but this shouldn't be much of an issue in most cases. If your tempdb database is not on its own disk or disk array, then don't use this option, as it can actually slow performance. [2000] Added 10-19-2000

28. SQL Server 2000 Enterprise Edition (not the standard edition) offers the ability to create indexes in parallel, greatly speeding index creation. Assuming your server has multiple CPUs, SQL Server 2000 uses near-linear scaling to boost index creation speed. For example, using two CPUs instead of one CPU almost halves the speed it takes to create indexes. [2000] Added 12-19-2000

29. As you probably already know, indexes on narrow columns are preferable to indexes on wide columns. The narrower the index, the more entries SQL Server can fit on a data page, which in turn reduces the amount of I/O required to access the data. But sometimes the column you want to search on using an index is much wider than desirable.
For example, let's say you have a music database that lists the titles of over 5,000,000 songs, and that you want to search by song title. Also assume that the column used to store the music titles is a VARCHAR(45). Forty-five characters is a very wide index, and creating an index on such a wide column is not wise from a performance perspective. So how do we deal with such a scenario?
SQL Server 2000 offers a new function called CHECKSUM. The main purpose for this function is to create what are called hash indices. A hash indices is an index built on a column that stores the checksum of the data found in another column in the table. The CHECKSUM function takes data from another column and creates a checksum value. In other words, the CHECKSUM function is used to create a mostly unique value that represents other data in your table. In most cases, the CHECKSUM value will be much smaller than the actual value. For the most part, checksum values are unique, but this is not guaranteed. It is possible that two slightly different values may produce the same identical CHECKSUM value.
Here's how this works using our music database example. Say we have a song with the title "My Best Friend is a Mule from Missouri". As you can see, this is a rather long value, and adding an index to the song title column would make for a very wide index. But in this same table, we can add a CHECKSUM column that takes the title of the song and creates a checksum based on it. In this case, the checksum would be 1866876339. The CHECKSUM function always works the same, so if you perform the CHECKSUM function on the same value many different times, you would always get the same result.
So how does the CHECKSUM help us? The advantage of the CHECKSUM function is that instead of creating a wide index by using the song title column, we create an index on the CHECKSUM column instead. "That's fine and dandy, but I thought you wanted to search by the song's title? How can anybody ever hope to remember a checksum value in order to perform a search?"
Here's how. Take a moment to review this code:

SELECT title, artist, composer
FROM songs
WHERE title = 'My Best Friend is a Mule from Missouri'
AND checksum_title = CHECKSUM('My Best Friend is a Mule from Missouri')

In this example, it appears that we are asking the same question twice, and in a sense, we are. The reason we have to do this is because there may be checksum values that are identical, even though the names of the songs are different. Remember, unique checksum values are not guaranteed.
Here's how the query works. When the Query Optimizer examines the WHERE clause, it determines that there is an index on the checksum_title column. And because the checksum_title column is highly selective (minimal duplicate values) the Query Optimizer decides to use the index. In addition, the Query Optimizer is able to perform the CHECKSUM function, converting the song's title into a checksum value and using it to locate the matching records in the index. Because an index is used, SQL Server can very quickly locate the rows that match the second part of the WHERE clause. Once the rows have been narrowed down by the index, then all that has to be done is to compare these matching rows to the first part of the WHERE clause, which will take very little time.
This may seem a lot of work to shorten the width of an index, but in many cases, this extra work will pay off in better performance in the long run. Because of the nature of this tip, I suggest you experiment using this method, and the more conventional method of creating an index on the title column itself. Since there are so many variables to consider, it is tough to know which method is better in your particular situation unless you give them both a try. [2000] Added 3-6-2001

30. There is a bug in SQL Server 7.0 and 2000 that has yet to be corrected, that can negatively affect the performance of some queries. Queries that have multiple OR clauses in them that are based on a clustered composite index may ignore the index and perform a table scan instead. This bug only appears if the query is within a stored procedure, or if it is executed through an ODBC-based application, such as VB, ASP, or Microsoft Access.
The best way to identify if you are experiencing this bug is to view the Query Plan for slow queries that fit the criteria above, and see if a table scan is being performed, or if the index is being used.
There are five different possible workarounds for this bug, depending on your circumstances:
• Use an appropriate index hint to force the use of the composite index. This is probably the easiest method to get around this problem, and the one I recommend.
• Changing from a clustered composite index to a non-clustered composite index may help, but this is not guaranteed. You will have to test it for yourself.
• Rewrite the query using a UNION clause to combine the results returned from the OR clauses. Of course, using a UNION clause may itself degrade performance. You will have to test this option to see if it is faster or not.
• If the query is being executed from an ODBC application through the SQLPrepare function with the SQL Server ODBC driver version 3.6 or earlier, then you can disable the "Generate Stored Procedures for Prepared Statements" option to workaround the bug.
• If the query is being executed from an ODBC application through either the SQLPrepare or SQLExecDirect functions with a parameterized query using the SQL Server ODBC driver version 3.7, you can use the odbccmpt utility to enable the SQL Server 6.5 ODBC compatibility option and also disable the "Generate Stored Procedures for Prepared Statements" option to workaround the bug.

SQL SERVER PERFORMANCE TUNING

2009-07-21T23:54:00.002+05:30

SQL Server Performance Tuning Tips for Stored Procedures

1. Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. This not only reduces network traffic (only the EXECUTE or CALL is issued over the network), but it can speed up the Transact-SQL because the code in the stored procedure residing on the server is already pre-compiled. In addition, after a stored procedure is run for the first time, it stays cached in SQL Server’s memory where it can potentially be reused, further reducing overhead on the SQL Server.
Keep in mind that just because you use a stored procedure does not mean that it will run fast. The code you use within your stored procedure must be well designed for both speed and reuse. [6.5, 7.0, 2000] Updated 5-18-2001

2. To help identify performance problems with stored procedures, use the SQL Server's Profiler Create Trace Wizard to run the "Profile the Performance of a Stored Procedure" trace to provide you with the data you need to identify poorly performing stored procedures. [7.0, 2000]

3. Include in your stored procedures the statement, "SET NOCOUNT ON". If you don't turn this command on, then every time a SQL statement is executed, SQL Server will send a response to the client indicating the number of rows affected by the statement. It is rare that this information will ever be needed by the client. Using this statement will help reduce the traffic between the server and the client. [6.5, 7.0, 2000]

4. Keep Transact-SQL transactions as short as possible. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application. Two ways to help reduce the length of a transaction are to: 1) break up the entire job into smaller steps so each step can be committed as soon as possible; and 2) take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server. [6.5, 7.0, 2000]

5. When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server's buffer. If the same stored procedure is called again from the same connection, it will used the cached query plan instead of creating a new one, saving time and boosting performance. This may or may not be what you want.
If the query in the stored procedure is exactly the same each time, then this is a good thing. But if the query is dynamic (the WHERE clauses changes from one execution of the stored procedure to the next), then this is a bad thing, as the query will not be optimized when it is run, and the performance of the query can suffer greatly.
If you know that your query will vary each time it is run from the stored procedure, you will want to add the WITH RECOMPILE option when you create the stored procedure. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized each time it is run. [6.5, 7.0, 2000]

6. Design your application to allow your users to cancel running queries. Not doing so may force the user to reboot the client, which can cause unresolvable performance problems. [6.5, 7.0, 2000]

7. Many stored procedures have the option to accept multiple parameters. This in and of itself is not a bad thing. But what can often cause problems is if the parameters are optional, and the number of parameters varies greatly each time the stored procedure runs. There are two ways to handle this problem, the slow performance way and fast performance way.
If you want to save your development time, but don't care about your application's performance, you can write your stored procedure generically so that it doesn't care how many parameters it gets. The problem with this method is that you may end up unnecessarily joining tables that don't need to be joined based on the parameters submitted for any single execution of the stored procedure.
Another, much better performing way, although it will take you more time to code, is to include IF...ELSE logic in your stored procedure, and create separate queries for each possible combination of parameters that are to be submitted to the stored procedure. This way, you can be sure you query is as efficient as possible each time it runs. [6.5, 7.0, 2000] Added 12-29-2000

8. Here's another way to handle the problem of not knowing what parameters your stored procedure might face. In fact, it will probably perform faster than the tip listed above.

Although the above tip is a good starting point, it's not complete. The problem is the query-plans, the pre-compilation of stored procedures, that SQL Server does for you. As you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them. The problem that arises with the above tip is that SQL Server will only generate a query-plan for the path taken through your stored procedure when you first call it, not all possible paths.

Let me illustrate this with an example. Consider the following procedure (pre-compilation doesn't really have a huge effect on the queries used here, but these are just for illustration purposes):

CREATE PROCEDURE dbo.spTest (@query bit) AS
IF @query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO

Suppose I make my first call to this procedure with the @query parameter set to 0. The query-plan that SQL Server will generate will be optimized for the first query ("SELECT * FROM authors"), because the path followed on the first call will result in that query being executed.
Now, if I next call the stored procedure with @query set to 1, the query plan that SQL Server has in memory will not be of any use in executing the second query, since the query-plan is optimized for the authors table, not the publishers table. Result: SQL Server will have to compile a new query plan, the one needed for the second query. If I next call the procedure with @query set to 0 again, the whole path will have to be followed from the start again, since only one query-plan will be kept in memory for each stored procedure. This will result in sub-optimal performance.

As it happens I have a solution, one that I've used a lot with success. It involves the creation of what I like to call a 'delegator'. Consider again spTest. I propose to rewrite it like this:

CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO

CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO

CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO

The result of this restructuring will be that there will always be an optimized query-plan for spTestFromAuthors and spTestFromPublishers, since they only hold one query. The only one getting re-compiled over and over again is the delegator, but since this stored procedure doesn't actually hold any queries, that won't have a noticeable effect on execution time. Of course re-compiling a plan for a simple 'SELECT *' from a single table will not give you a noticeable delay either (in fact, the overhead of an extra stored procedure call may be bigger then the re-compilation of "SELECT * FROM AnyTable"), but as soon as the queries get bigger, this method certainly pays off.

The only downside to this method is that now you have to manage three stored procedures instead of one. This is not that much of a problem though as the different stored procedures can be considered one single 'system', so it would be logical to keep all of them together in the same script, which would be just as easy to edit as a single stored procedure would be. As far as security is concerned, this method shouldn't give you any extra headaches either, as the delegator is the only stored procedure directly called by the client, this is the only one you need to manage permissions on. The rest will only be called by the delegator, which will always work as long as those stored procedures are owned by the same user as the delegator.

I've had large successes using this technique. Recently I developed a (partial full-text) search engine for our reports database, which resulted in a stored procedure that originally ran about 20 seconds. After employing the above technique, the stored procedure only took about 2 seconds to run, resulting in a ten-fold increase in performance! [6.5, 7.0, 2000] Contributed by Jeremy van Dijk. Added 8-15-2000

9. While temporary stored procedures can provide a small performance boost in some circumstances, using a lot of temporary stored procedures in your application can actually create contention in the system tables and hurt performance. Instead of using temporary stored procedures, you may want to consider using the SP_EXECUTESQL stored procedure instead. It provides the same benefits on temporary stored procedures, but it does not store data in the systems tables, avoiding the contention problems. [7.0, 2000]

10. If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix "sp_" in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database. Since it is not there, time is wasted looking for the stored procedure.
If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_". [6.5, 7.0, 2000] Tip contributed by Joey Allen.

11. Before you are done with your stored procedure code, review it for any unused code that you may have forgotten to remove while you were making changes, and remove it. Unused code just adds unnecessary bloat to your stored procedures. [6.5, 7.0, 2000] Added 8-15-2000

12. For best performance, all objects that are called within the same stored procedure should all be owned by the same owner, preferably dbo. If they are not, then SQL Server must perform name resolution on the objects if the object names are the same but the owners are different. When this happens, SQL Server cannot use a stored procedure "in-memory plan" over, instead, it must re-execute the stored procedure, which hinders performance. [7.0, 2000] Added 10-12-2000

13. When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql offers to major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.
Sp_executesql executes a string of Transact-SQL in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.
Learn more about how to use sp_executesql in the SQL Server Books Online. [7.0, 2000] Added 3-7-2001

14. SQL Server will automatically recompile a stored procedure if any of the following happens:
• If you include a WITH RECOMPILE clause in a CREATE PROCEDURE or EXECUTE statement.
• If you run sp_recompile for any table referenced by the stored procedure.
• If any schema changes occur to any of the objects referenced in the stored procedure. This includes adding or dropping rules, defaults, and constraints.
• New distribution statistics are generated.
• If you restore a database that includes the stored procedure or any of the objects it references.
• If the stored procedure is aged out of SQL Server's cache.
• An index used by the execution plan of the stored procedure is dropped.
• A major number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure.
• The stored procedure includes both DDL (Data Definition Language) and DML (Data Manipulation Language) statements, and they are interleaved with each other.
• If the stored procedure performs certain actions on temporary tables.

15. One hidden performance problem of using stored procedures is when a stored procedure recompiles too often. Normally, you want a stored procedure to compile once and to be stored in SQL Server's cache so that it can be re-used without it having to recompile each time it is used. This is one of the major benefits of using stored procedures. But in some cases, a stored procedure is recompiled much more often than it needs to be recompiled, hurting your server's performance. In fact, it is possible for a stored procedure to have to be recompiled while it is executing! Here are three potential problems you want to look out for when writing stored procedures.
Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update
If your database has the "Auto Update Statistics" database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions. One side effect of this is that this also causes any stored procedures that reference these tables to be recompiled. Again, this is normal, as you don't want a stored procedure to be running an outdated query plan. But again, sometimes stored procedures recompile more than they have to. Here are some suggestions on how to reduce some of the unnecessary recompilations:
• Use sp_executesql instead of EXECUTE to run Transact-SQL strings in your stored procedures.
• Instead of writing one very large stored procedure, instead break down the stored procedure into two or more sub-procedures, and call then from a controlling stored procedure.
• If your stored procedure is using temporary tables, use the KEEP PLAN query hint, which is used to stop stored procedure recompilations caused by more than six changes in a temporary table, which is the normal behavior. This hint should only be used for stored procedures than access temporary tables a lot, but don't make many changes to them. If many changes are made, then don't use this hint.
Unnecessary Stored Procedure Recompilations Due to Mixing DDL and DML Statements in the Same Stored Procedure
If you have a DDL (Data Definition Language) statement in your stored procedure, the stored procedure will automatically recompile when it runs across a DML (Data Manipulation Language) statement for the first time. And if you intermix both DDL and DML many times in your stored procedure, this will force a recompilation every time it happens, hurting performance.
To prevent unnecessary stored procedure recompilations, you should include all of your DDL statements at the first of the stored procedure so they are not intermingled with DML statements.
Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations
Improper use of temporary tables in a stored procedure can force them to be recompiled every time the stored procedure is run. Here's how to prevent this from happening:
• Any references to temporary tables in your stored procedure should only refer to tables created by that stored procedure, not to temporary tables created outside your stored procedure, or in a string executed using either the sp_executesql or the EXECUTE statement.
• All of the statements in your stored procedure that include the name of a temporary table should appear syntactically after the temporary table.
• The stored procedure should not declare any cursors that refer to a temporary table.
• Any statements in a stored procedure that refer to a temporary table should precede any DROP TABLE statement found in the stored procedure.
• The stored procedure should not create temporary tables inside a control-of-flow statement.

16. To find out if your SQL Server is experiencing excessive recompilations of stored procedures, a common cause of poor performance, create a trace using Profiler and track the SP:Recompile event. A large number of recompilations should be an indicator if you potentially have a problem. Identify which stored procedures are causing the problem, and then take correction action (if possible) to reduce or eliminate these excessive recompilations. [7.0, 2000] Added 9-13-2001

17. Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS) instead of being called directly from your application. A stored procedure can be reused from the procedure cache only if the connection settings calling the stored procedure are the same. If different connections call a stored procedure, SQL Server must load a separate copy of the stored procedure for each connection, which somewhat defeats the purpose of stored procedures. But if the same connection calls a stored procedure, it can be used over and over from the procedure cache. The advantage of Transaction Server is that it reuses connections, which means that stored procedures can be reused more often. If you write an application where every user opens their own connection, then stored procedures cannot be reused as often, reducing performance. [7.0, 2000] Added 10-12-2000

18. Avoid nesting stored procedures, although it is perfectly legal to do so. Nesting not only makes debugging more difficult, it makes it much more difficult to identify and resolve performance-related problems. [6.5, 7.0, 2000] Added 10-12-2000

19. If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation problem and the client applications needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance. [6.5, 7.0, 2000] Added 10-12-2000

20. When calling a stored procedure from your application, it is important that you call it using its fully qualified name. Such as:
exec database_name.dbo.myProcedure
instead of:
exec myProcedure
Why? There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to eliminate any potential confusion about which stored procedure you want to run, helping to prevent bugs and other potential problems. But more importantly, doing so allows SQL Server to access the stored procedures execution plan more directly, and in turn, speeding up the performance of the stored procedure. Yes, the performance boost is very small, but if your server is running tens of thousands or more stored procedures every hour, these little time savings can add up. [7.0, 2000] Added 3-7-2001 More from Microsoft

21. If a stored procedure needs to return only a single value, and not a recordset, consider returning the single value as an output statement. While output statements are generally used for error-checking, they can actually be used for any reason you like. Returning a single value as at output statement is faster than returning a single value as part of a recordset. [6.5, 7.0, 2000] Added 8-1-2001

SQL SERVER QUERY TIPS - II

2009-07-21T23:50:00.003+05:30

Query to a Text File

Question: I would like to create a stored procedure that runs a query and outputs the results to a text file and allows me to add extra delimeters and static field info.

Answer: SQL Server has no native command for outputting query results to a file. You can use the extended
stored procedure xp_cmdshell and call isql (command-line SQL) with your query and output the results to a file. Any delimiters would need to be part of the SELECT string:

DECLARE @isqlString varchar(255)

SELECT @isqlString = 'isql -Q "SELECT DateCol FROM NorthwindTest.dbo.Test" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @isqlString

Dynamic Variables and Stored Procedures

Question: I need to pass the @TheVar variable to the stored procedure's IN statement but I keep getting zero records. If I type this statement into the SQL... WHERE Name IN ('John', 'Frank', 'Tom') the statement works fine; 20 records are returned.

EXECUTE the_proc "'John', 'Frank', 'Tom'"

CREATE PROCEDURE the_proc
@TheVar nvarchar(40)
AS SELECT COUNT(FieldName) FROM Clients WHERE Name IN (@TheVar)
Do you have any ideas? I use MS SQL Server 7.0 SP2.

Answer: If you want to use variables as part of your SQL statement you need to modify it to call the EXECUTE statement. I created a Clients table with a single Name field of varchar(255) with records for John, Frank, and Tom. Then I created a stored procedure (similar to yours) that uses the input variable to build the IN clause like this:

CREATE PROCEDURE GetClients
@TheVar varchar(255)
AS
EXEC ('SELECT COUNT(Name)
FROM Clients
WHERE Name IN (' + @TheVar + ')')

As you can see, it builds the SELECT statement using the variable and then the EXEC statement runs it.
The syntax to call this procedure is:

GetClients "'John', 'Tom', 'Frank'"
---
3

Question: If my SQL Server has the following specs: 6.05.02 SQL-DMO 6.50.252 DB-Library Which version do I have? And which service pack version do I have?

Answer: If you want to query the version of SQL Server that's currently running you can use the @@version variable:

SELECT @@version

This returns the version, processor, build and service pack information for the currently installed SQL Server. This information is stored in the system tables, and you can retrieve more details by calling the extended stored procedure xp_msver. Be sure to call it from the master database.

Data Type

Question: How do I compare two values when one value(data type) is char and the other is an integer?

Answer: For this you can use the CONVERT function. Since you can't convert character data to numbers,
you'll have to convert the integer column to character. Assuming you have a table called TestTable with two columns:

IntCol int
CharCol char(10)

with these values

IntCol CharCol
------ -------
1 1
2 2
3 xyz
4 5

You can use this query to SELECT rows based on a comparison:

SELECT * FROM TestTable WHERE CONVERT(char(10), IntCol) = CharCol

IntCol CharCol
----------- ----------
1 1
2 2

E-mail in a Query

Question: How do I send an e-mail to myself if a field reads "SEND_MAIL?"
For example, a user wants more info on a product, so he clicks on the Send Mail button, which in turn throws an entry into the database. This entry is SEND_MAIL. Now at the end of the day I would like the server to scan the database for the word SEND_MAIL and, when it finds one, send me an email with the name and email address of the person.

Answer: To answer this question I took the scenario of a Web site that logs user registrations and puts a
visitor's first name, last name and e-mail address in a table. I then created a stored procedure that selects
this data and e-mails it to an e-mail address provided as an input parameter. If the SendMail column in the table is Y, then the details will be e-mailed. The stored procedure is called like this:

usp_CheckForMail 'name@emailaddress.com'

The script to create the table is as follows:

if exists (select * from sysobjects where id = object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Fname] [varchar] (50) NULL ,
[Lname] [varchar] (50) NULL ,
[EmailAddress] [varchar] (100) NULL ,
[SendMail] [char] (1) NULL
) ON [PRIMARY]
GO

The script to create the stored procedure to send the e-mails is:

if exists (select * from sysobjects where id = object_id(N'[dbo].[usp_CheckForMail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_CheckForMail]
GO

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE usp_CheckForMail
@SendToEmail varchar(255)
AS
DECLARE @ID int, @Fname varchar(50), @Lname varchar(50), @EmailAddress varchar(100), @MsgText varchar(255)
DECLARE WebVisitors CURSOR FOR
SELECT ID, Lname, Fname, EmailAddress FROM TestTable

WHERE SendMail = 'Y'
ORDER BY Lname, Fname
OPEN WebVisitors

FETCH NEXT FROM WebVisitors
INTO @ID, @Lname, @Fname, @EmailAddress

EXEC master.dbo.xp_startmail /* typically mail will be started on your server */

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

-- PRINT 'Visitor: ' + @Fname + ' ' + @Lname + ' at ' + @EmailAddress + ' [ID=' + CAST(@ID AS varchar) + ']'

SET @MsgText = 'Visitor: ' + @Fname + ' ' + @Lname + ' at ' + @EmailAddress + ' [ID=' + CAST(@ID AS varchar) + ']'
EXEC master.dbo.xp_sendmail @recipients = @SendToEmail, @message =@MsgText, @subject = 'New Web Visitor'

FETCH NEXT FROM WebVisitors
INTO @ID, @Lname, @Fname, @EmailAddress

END

CLOSE WebVisitors
DEALLOCATE WebVisitors

GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

SQL QUERY TIPS - I

2009-07-21T23:39:00.004+05:30

Q. Execute an Operating System Command From Within SQL Server
A. The xp_cmdshell extended store procedure makes it possible to execute operating system commands from within SQL Server.

Q. How can I create a plain-text flat file from SQL Server as input to another application?
A. One of the purposes of Extensible Markup Language (XML) is to solve challenges like this, but until all
applications become XML-enabled, consider using our faithful standby, the bulk copy program (bcp) utility.
This utility can do more than just dump a table; bcp also can take its input from a view instead of from a table. After you specify a view as the input source, you can limit the output to a subset of columns or to a subset of rows by selecting appropriate filtering (WHERE and HAVING) clauses.

More important, by using a view, you can export data from multiple joined tables. The only thing you cannot do is specify the sequence in which the rows are written to the flat file, because a view does not let you include an ORDER BY clause in it unless you also use the TOP keyword.

If you want to generate the data in a particular sequence or if you cannot predict the content of the data you want to export, be aware that in addition to a view, bcp also supports using an actual query. The only "gotcha" about using a query instead of a table or view is that you must specify queryout in place of out in the bcp command line.

For example, you can use bcp to generate from the pubs database a list of authors who reside in California by writing the following code:

bcp "SELECT * FROM pubs..authors WHERE state = 'CA'" queryout c:\CAauthors.txt -c -T -S

Q. How can I programmatically detect whether a given connection is blocked?
A. A connection is blocked when it requires an object that another connection has a lock on. You can use the system stored procedure sp_lock to retrieve information about the current locks in SQL Server, and you can use the server process ID (SPID) to filter the information that sp_lock returns. To determine whether a given process is waiting for the release of a locked resource, you can execute the sp_GetBlockInfo procedure that follows.

Note: You must execute the procedure before the timeout.

USE master
GO

CREATE PROCEDURE sp_GetBlockInfo
@BlockedSPID as int
AS
IF EXISTS (select *
FROM master.dbo.syslockinfo
WHERE req_spid = @BlockedSPID
AND req_status = 3)

SELECT sli1.req_spid AS SPID,
SUBSTRING (u.name, 1, 8) As Mode,
DB_NAME(sli1.rsc_dbid) AS [Database],
OBJECT_NAME(sli1.rsc_objid) AS [Table],
sli1.rsc_Text AS [Resource]
FROM master.dbo.syslockinfo sli1
JOIN master.dbo.spt_values u
ON sli1.req_mode + 1 = u.number
AND u.type = 'L'
JOIN
master.dbo.syslockinfo sli2
ON sli1.rsc_dbid = sli2.rsc_dbid
AND sli1.rsc_objid = sli2.rsc_objid
AND sli1.rsc_text = sli2.rsc_text
WHERE sli2.req_spid = @BlockedSPID
AND sli1.req_status = 1
AND sli1.req_spid <> @BlockedSPID
AND sli2.req_status = 3
ELSE

SELECT CAST(1 as int) AS SPID,
SUBSTRING ('', 1, 8) AS Mode,
DB_NAME(NULL) AS [Database],
OBJECT_NAME(NULL) AS [Table],
CAST(NULL AS nchar(32)) AS [Resource]
WHERE 1=2

GO

The sp_GetBlockInfo procedure tells you the lock mode, the database and object names of the locked resource, and in the case of a blocking chain, which SPID is the root blocker. If the process is not blocked,
sp_GetBlockInfo returns an empty recordset.

You can also detect blocks by checking for error 1222, "Lock request time out period exceeded." The LOCK_TIMEOUT setting controls how long a process will wait for locks to be released before timing out. When the lock timeout occurs, SQL Server sends error 1222 to the application. In SQL Server 7.0, this error aborts the statement but does not cause the batch to roll back, so you can look for the Transact-SQL system variable @@ERROR and determine where locks exist

Q. Can you create UNIQUE and PRIMARY KEY constraints on computed columns in SQL Server 2000?
A. In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on computed columns,
you can create UNIQUE and PRIMARY KEY constraints on computed columns.

Defining a UNIQUE constraint on a computed column is a straightforward process, as the following example shows:

CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)

However, if you define a PRIMARY KEY on a computed column, such as:

CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)

you receive the following error:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'T2'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Because of the primary key constraint, SQL Server requires you to guarantee that your computation's
result will not be NULL. The computation in the computed column can overflow
(for example, when you add 1 to the largest integer) or underflow (when you subtract 1 from the smallest integer), and other computations can result in a divide-by-zero error. However, if the ARITHABORT
(which determines whether a query has ended when an overflow or a divide-by-zero error occurs)
and ANSI_WARNINGS (which specifies ANSI SQL-92 standard behavior for several error conditions)
session settings are off, instead of ending the query, the computation can have a NULL result.

In practice, when either ARITHABORT or ANSI_WARNINGS settings is off, you cannot create an index on a computed column or insert values into a table that has an index on a computed column because SQL Server detects such an attempt and returns an error. But SQL Server still requires you to guarantee that the computation will not result in NULL values. The trick is to wrap the computed column's computation with the ISNULL() function and supply an alternative value if the computation results in NULL:

CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)

Q. Why does my inline or embedded SQL run faster than my stored procedures?
A. Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:

SELECT * FROM dbo.mytable

This technique helps you reuse plans and prevent cache misses.

Q. Why doesn't SQL Server permit an ORDER BY clause in the definition of a view?
A. SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can't fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround:

USE pubs
GO

CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO

The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it is used in conjunction with the TOP keyword.

Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.

Q. Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number of rows from a query?
A. With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which it replaces only the last row. If the input is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly sorted means you're dealing with a heap with ordered inserts for the initial population and without many updates, deletes, forwarding pointers, and so on afterward. A nearly sorted heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a table with the same number of rows but with unordered inserts, TOP N was not as efficient anymore. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.

Q, The Difference between 'Count' and 'Count(*)'
A. 'Count': Counts the number of non-null values.
'Count(*)': Counts the number of rows in the table, including null values and duplicates.

Q. I have two tables t1 and t2 both with the columns a1, a2. I want to find the difference of (the set of t1) - (the set of t2) without using the keyword EXCEPT because MSSQL 2000 does not recognize that word. I have tried this query but it does not give me what I want: SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.* FROM t1 INNER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)
A. SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)

This is the solution. The difference of (the set of t1) - (the set of t2) is

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2 WHERE t2.a1 IS NULL AND t2.a2 IS NULL

According to SQL Query Analyzer, this is slightly more efficient than the left join (possibly only because
of the tables I tested it with):

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.a1=t2.a1 AND t1.a2=t2.a2)

Q, Leading Zero's in Stored Procedure
I have a form and I am passing a value to a stored procedure. The value has leading zeros. When the values are passed the leading zeros are dropped, thus causing my stored procedure to blow up. Is there a way to maintain those zeros in passing or pick them up again in the procedure?

Try to pass value as string.

If you were passing a string value then the LZ should not be dropped, so I suspect that you are passing a
numeric value then converting it back to a varchar which will drop any LZ. If you don't want to change the interface then you can always restore the LZ in the stored procedure by using the following.

declare @lz varchar(10)
declare @numeric numeric(10,2)
declare @result varchar(20)
select @lz = '0000000000'
select @numeric = 0000123.45
-- the LZ will be lost upon assignment

select @result = substring(@lz, 1, datalength(@lz)-datalength(convert(varchar

Q, Method to Perform Case Sensitive Searches in SQL Server
A. By default, SQL Server 7.0 installation sets up SQL Server to run in a case insensitive mode. For most applications this may work great, but there are certain situations were case sensitive searches are required. For instance, if a web site needs to have passwords that are case sensitive a method needs to be devised to perform case-sensitive queries. The following script performs a case-sensitive search. First let’s create a table:

CREATE TABLE test
(
id INT NOT NULL,
msg VARCHAR(100) NOT NULL
)

Now let’s insert some case sensitive data into it:

INSERT INTO test (id, msg) VALUES (1, 'bUSY');
INSERT INTO test (id, msg) VALUES (2, 'BUSY');
INSERT INTO test (id, msg) VALUES (3, 'busy');

In our test we are searching for a ‘bUSY’ value in the msg column of the test table. So the syntax of the same query, if the SQL Server was set to be case sensitive, would be:

SELECT * FROM test where msg = ‘bUSY’;

This query will return all rows in the test table. Now, here is the script that will perform the case sensitive search.

DECLARE @table VARCHAR( 30 )
DECLARE @col VARCHAR( 30 )
DECLARE @searchVal VARCHAR( 195 )

SET @table = 'test'
SET @col = 'msg'
SET @searchVal = 'bUSY'

DECLARE @sql VARCHAR( 255 )
DECLARE @colLength VARCHAR( 3 )

SELECT @colLength = CONVERT( varchar(3), DATALENGTH( @searchVal ) )

SELECT @sql = 'SELECT * ' +
' FROM ' + @table +
' WHERE' +
' CONVERT( varbinary( ' + @colLength + '), ' + @col + ') = ' +
' CONVERT( varbinary( ' + @colLength + '), "' + @searchVal + '")' +
' AND ' + @col + '="' + @searchVal + '"'

EXEC( @sql )

Listing Available Tables

Q, How do I list the available tables in a database I'm querying?
A. You can get a list of tables in a database by calling the INFORMATION_SCHEMA.Tables view like this:

SELECT * FROM information_schema.tables

This returns the name of the current database, the owner, the table name, and the table type for each table in the database. It's possible to query the system tables directly, but if this gives the information you need,
it's better to use the existing views that come with SQL Server.

How do I count the number of duplicate items in a table?
Lets break down your question into several steps. First, let's create a sample table using the following code:

create table dups
(
i int
)
go

declare @i int

@i = 0

while (@i < 35)
begin
insert into dups(i) values (cast (rand() * 50 as int))
select @i = @i + 1
end

Now, let's find rows that are duplicates. For that we can use a simple group by statement:

select i,count(*) as num_records from dups group by i having count(*) > 1

My sample data produced the following result set:

i num_records
----------- -----------
0 2
5 2
18 2
22 2
27 2
31 2
34 2
44 2
49 2

This identifies the rows that have duplicates. But it does not return the total number of duplicates in the table. The first change we must make is to recognize the above rows that show 2 contain only one duplicate.
So we want a query that basically sums up the duplicates from the above query. To do so, we take the previous query and can put that in the from statement as a derived table. We then can use the sum function to create the total for us:

select sum(num_dups)
from (select i,count(*)-1 as num_dups
from dups
group by i
having count(*)-1 > 0) as mydups

Using Distributed Queries on SQL Server 7.0
SQL Sever 7 allows developers to execute commands against OLE DB data sources on different servers. In order to execute commands on remote servers, the SQL Server instance where the commands will be issued must be setup properly. This entails adding the remote server to SQL Server's linked server list. Do this by using the sp_addlinkedserver command.

For example, to link a remote SQL Server database that resides on the RemoteDBServer server, you would usethe following syntax:

EXEC master. . sp_addlinkedserver
@server = 'RemoteDBServer',
@provider = 'SQLOLEDB',
@srvproduct = 'SQL Server',
@datasrc = 'RemoteDBServer'

Note that only members of the sysadmin role can set this server option. Once the remote database has been linked, queries can be executed against it as long as the remote tables are prefaced using Server.Database.Table Owner.Table Name.

For example, the following query would return all rows in the authors table of our RemoteDBServer SQL Server database:

SELECT * FROM RemoteDBServer.pubs.dbo.authors

Dynamic Query with .... in (@var)

Can I use a variable in a query with the IN clause (a,b,c..z), without getting quotes or conversion errors?
You can use a variable as your IN clause, but this requires that you use the EXEC function to run the statement.

Delete Files from SQL Server Local Machine

Ever wanted to delete files from the local machine that your SQL Server database is running?
You can do it using the extended stored procedure xp_cmdshell like this:

EXEC master..xp_cmdshell 'del C:\file.txt'

But this requires the sysadmin option on the SQL Server and Admin role from NT server. In most instances it is not preferable to give these privileges. So to delete files without requiring this access use the built-in SQL Server Automation APIs and the FileSystemObject:

DECLARE @hr int
DECLARE @ole_FileSystem int

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT

EXEC @hr = sp_OAMethod @ole_FileSystem, 'DeleteFile', NULL, 'C:\file.txt'

EXEC @hr = sp_OADestroy @ole_FileSystem

Large Text Fields

How do I create a text field of greater than 8,000 characters (in v7.0)? I attempted to use the "text" data type, but my code returned an error saying the maximum size was 8,000. Here's the code:
CREATE TABLE X ( X_ID int IDENTITY(1,1), X_DESC text (60000) NOT NULL ) GO

Answer: SQL Server is returning a bogus error message. The real error has to do with your syntax.
When specifying text you don't specify a size.

You can see the real error message if you reduce the number 60000 to 5. Then you will get this message:
Server: Msg 2716, Level 16, State 1, Line 1

Column or parameter #2: Cannot specify a column width on data type text. Instead, simply specify it as text without the parentheses and the number. The actual size of the storage used for the text field will depend on how much data you actually put in the column

Importing Excel into SQL without Using Wizard

How do I give a user the option of importing Excel and a delimited text file into a SQL Server Database without manually using SQL DTS?
You can use the DTS object model to programmatically create, modify and run DTS packages.
You can do this many ways, but essentially this object model has objects for anything you can do in DTS.
If you already have the package created and saved as a file, add a reference to the DTS Library.
Then you can call it like this:

Dim Package As New DTS.Package

Package.LoadFromStorageFile App.Path "\DTSPackage.dts"
Package.Execute

Connecting to SQL Server 7 via MS-DOS 6.2

I have some complex engine control software that has to run under MS-DOS 6.2, but it needs to transfer data to SQL Server 7. What's the best way to connect to SQL Server from DOS?
You can use OSQL or ISQL to connect to SQL Server from the command line. Both of these utilities are Win32 command-line utilities that allow you to log in to a server and issue any SQL command. The OSQL utility supports the most functionality and uses ODBC to connect to SQL Server.

The syntax "osql/?" provides a listing of all OSQL flags.

This syntax connects to a server and issues a query:

osql -S "servername" -U "sa" -d "pubs" -q "SELECT * FROM Authors"

To insert data into a database, you could put OSQL commands in a batch file or provide an input file of
SQL INSERT commands. See the SQL Server BOL for details and syntax of OSQL. If you have a straight DOS box, you need to install the 16-bit client that comes with SQL Server 6.5. Both the client and the 6.5 version of ISQL use DB-Lib, which doesn't support some SQL Server 7 features like unicode columns, varchar columns larger than 255, etc.

Passing a CSV List Within an IN Statement
I have a CSV list I am passing through as a parameter in a stored procedure.
Example of Region list: 1,2,3,4,5,6,7,8,9,10
CREATE PROCEDURE sp_getReport
@Regionlist varchar(100) AS
Select * from tblRegion where Region_ID IN (@regionlist)

The varchar treats the list as one block of data. How can I change my code/syntax to read each value of the list separately within the IN statement?

Answer: When you pass variables that are part of the statement in a stored procedure, you need to use
the EXEC(UTE) statement. Here's the syntax to call the stored procedure and modify the current stored procedure to take the regions list as a parameter (the only difference is I called the column RegionID instead of Region_ID):

sp_getreport '1,2,3,4,5,6,7,8,9,10'

CREATE PROCEDURE sp_getReport
@regionlist varchar(100)
AS

EXEC ('Select * from tblRegion where RegionID IN (' + @regionlist +')')

Creating Tables with Variable Names

Is it possible to create tables in stored procedures using a variable for the table name?
For instance:

declare @MyName
set @MyName = 'dbo.Unique_Name'
create table @MyName ....
This obviously doesn't work, but does this ability exist?

Answer: Yes, you can issue DLL statements with variables; you just need to use the EXECUTE statement.
For example, this statement uses a variable for the table name, as in your example:

DECLARE @TableName varchar(255)
SET @TableName = '[dbo].[TestTable]'

EXEC ('CREATE TABLE ' + @TableName + ' (
[ID] [int] NULL ,
[ItemDesc] [char] (50) NULL
)')

Use Sysobjects in SQL Server to Find Useful Database Information

SQL Server sysobjects Table contains one row for each object created within a database. In other words,
it has a row for every constraint, default, log, rule, stored procedure, and so on in the database.
Therefore, this table can be used to retrieve information about the database. We can use xtype column in
sysobjects table to get useful database information. This column specifies the type for the row entry in sysobjects.

For example, you can find all the user tables in a database by using this query:

select * from sysobjects where xtype='U'

Similarly, you can find all the stored procedures in a database by using this query:

select * from sysobjects where xtype='P'

This is the list of all possible values for this column (xtype):

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

FAQ's : SQL SERVER DBA

2009-07-21T23:22:00.003+05:30

Hi buddies,

Here are some useful FAQs on SQL SERVER DBA.

Explain the concepts and capabilities of SQL Server.
Microsoft SQL server is a relational database management system. It uses MS- SQL as the query language. SQL Server offers a high level of security, reliability and scalability depending on the business needs..............

Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.
WITH ENCRYPTION Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an encrypted format. Users that have no access to system................

What is a linked server in SQL Server?
A linked server allows remote access. Using this, we can issue distributed queries, update, commands, and transactions across different data sources................

Features and concepts of Analysis Services
Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing...........

What is Analysis service repository?
Each server running analysis service has a repository to store objects of the computer running Analysis Services an Analysis service repository stores the information about the.............

What is SQL service broker?
SQL service broker provides asynchronous queuing functionality to SQL server. Once message is sent to the SQL server..........

What is user defined datatypes and when you should go for them?
User defined datatypes is created by using base SQL Server data type by providing a descriptive name............

What is bit datatype?
Bit datatype is used to store boolean information.............

What is lock escalation?
Lock escalation from SQL Server 7.0 onwards, is dynamically managed by SQL Server. It is..........

What is blocking?
Blocking happens when one connection from an application holds a lock and a second............

What is Public Role in SQL Server?
Every database has a public role which holds all the default permissions for the users in a database.................

Discuss about SQL Server Login.
SQL server login is used to connect to SQL server. This used when login in through the windows login credentials is not existent.............

Discuss about Builtin\Administrator.
The built in Administrator Account is basically used during some setup to join some machine in the domain............

Failover clustering overview
Failover clustering is mainly used for data availability. Typically in a failover cluster, there are two machines. One machine provides the basic services and the second is available to run..................

Describe the XML support SQL server extends.
SQL server can return XML document using FOR XML clause......

Explain in brief how SQL server enhances scalability of the database system.
SQL Server has efficient ways to enhance scalability of the database system......

What is SQL Server English Query?
SQL Server English Query helps to build applications that can accept query.....

What is the purpose of SQL Profiler in SQL server?
SQL Profiler captures SQL Server events from a server. The events are saved.....

What are the ways available in SQL Server to execute SQL statements?
SQL Server uses different ways to execute SQL statements which are listed below......

Explain Full-Text Query in SQL Server.
SQL Server supports searches on character string columns using Full-Text Query......

Explain the phases a transaction has to undergo.
The several phases a transaction has to go through are listed here. Database.....

What is XPath?
XPath is a language defined by the W3C, used to select nodes from XML documents. ....

Define the rules for designing Files and File groups in SQL Server.
A file or file group can only be used by one database. For example, the files abc.mdf and abc.ndf contains....

What are the Authentication Modes in SQL Server?
SQL Server supports two security (authentication) modes....

Explain Data Definition Language, Data Control Language and Data Manipulation Language.
Data definition language is used to define and manage all attributes and properties of a database.....

What are the steps to process a single SELECT statement?
SQL Server uses the following steps to process a single SELECT statement....

What are the restrictions while creating batches in SQL Server?
CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements....

Explain GO Command.
GO Command is used to signal the end of a batch.....

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.....

What is the difference between UNION and UNION ALL?
UNION command selects distinct and related information from two tables. On the other hand........

What is use of DBCC Commands?
Database Consistency Checker Commands give details in form of statistics about the SQL Server.........
What is Log Shipping?
UNION command selects distinct and related information from two tables. On the other hand........
What is the difference between a Local and a Global temporary table?
A local temporary table lives until the connection is valid or until the duration of a compound statement.........
What is the STUFF and how does it differ from the REPLACE function?
STUFF function is used to insert a string into another string by deleting some characters specified.........

What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?
A table can have only one ROWGUIDCOL column......

What is the actions prevented once referential integrity is enforced?
Following are the actions prevented once referential integrity is enforced......

What are the commands available for Summarizing Data in SQL Server?
We have CUBE or ROLLUP operators to generate summary reports. Both are part of the GROUP BY....

List out the difference between CUBE operator and ROLLUP operator
CUBE generates a result set that represents aggregates for all combinations of values in the selected columns.......

What are the guidelines to use bulk copy utility of SQL Server?
While importing data, the destination table must already exist......

What are the capabilities of Cursors?
Cursors can support various functionalities that are listed here.....

What are the ways to controlling Cursor Behavior?
Cursors behavior can be controlled by dividing them into cursor types: forward-only, static,........

What are the advantages of using Stored Procedures?
Stored procedures provide performance benefits through local storage, precompiling the code, and caching......

What are the ways to code efficient transactions?
Database users can have permission to execute a stored procedure without being....

What are the differences among batches, stored procedures, and triggers?
A batch is a group of one or more SQL statements. SQL Server compiles the statements......

What security features are available for stored procedures?
Database users can have permission to execute a stored procedure without being......

What are the instances when triggers are appropriate?
What are the restrictions applicable while creating views?
Views can be created referencing tables and views only in the current database.......

What are the events recorded in a transaction log?
The start and end of each transaction......

Describe when checkpoints are created in a transaction log.
It is created when CHECKPOINT statement is executed.......

Define Truncate and Delete commands.
Truncate command is used to remove all rows of the column.The removed records are not recorded in the transaction log......

What are the lock types?
Shared Lock allows simultaneous access of record by multiple Select statements. Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading......

Define Identity and uniqueidentifier property of Column.
Column with identity property contains unique system generated value in the table. Column with identity property is similar to AutoNumber field in MS Access....

Describe in brief system database.
Master database is system database. It contains information about server’s configuration. It is a very important database and important to backup Master.....

Define Stuff Function and Replace Function. Describe in brief SQL Server monitoring ways.
SQL Profiler is a tool that stores events from the server. SQL Profiler saves the events in the trace file......

Define transaction and transaction isolation levels.
A transaction is a set of operations that works as a single unit. The ransactions can be categorized into explicit, autocommit, and implicit....

SQL Server Optimization Tips
Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic......

Having clause and Where clause
Both are used for filtering of records based on filtered criteria.....

Describe in brief authentication modes in SQL server.
This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT.....

Define @@Error and raiseerror.
It is system variable that returns error code of the SQL statement.....

Describe in brief Databases and SQL Server Databases Architecture.
A database is a structured collection of data. Database can be thought as simple data file......

Define Normalization and De- Normalization.
It is the process of organizing data into related table. To normalize database, we divide database into tables.....

What is transact-SQL? Describe its types?
SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML....

Define database objects.
SQL Server database stores information in a two dimensional objects of rows and columns called table......

Define data, entity, domain and referential integrity.
Data Integrity validates the data before getting stored in the columns of the table. SQL Server supports four type of data integrity.....

Describe in brief exporting and importing utility?
The Bulk Copy is a command utility that transfer SQL data to or from a data file.
This utility mostly used to transfer huge data to SQL server from other database....

Define Distributed Query and Linked Server?
Distributed Query is a query which can retrieve data from multiple data sources including distributed data........

Define temporary and extended stored procedure.
Temporary Stored Procedure is stored in TempDB database. It is volatile and is deleted once connection gets terminated or server is restarted......

Define Primary and Unique key.
The column or columns of the table whose value uniquely identifies each row in the table is called primary key. You can define column as primary key using primary key constraint while you create table.....

What is index? Define its types.
Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record.........

SQL Server Interview Questions

2009-07-17T11:53:00.010+05:30

Interview Questions on Database Concepts

(Q) What is a database or database management system (DBMS)?

(Q) What is the difference between DBMS and RDBMS?

(DB) What are COD rules?

(Q) Is Access database a RDBMS?

(Q) What is the main difference between ACCESS and SQL SERVER?

(Q) What is the difference between MSDE and SQL SERVER 2000?

(Q) What is SQL SERVER Express 2005 Edition?

(DB) What is SQL Server 2000 Workload Governor?

(Q) What is the difference between SQL SERVER 2000 and 2005?

(Q) What are E-R diagrams?

(Q) How many types of relationships exist in database designing?

(Q) What is normalization? What are the different type of normalization?

(Q) What is denormalization?

(DB) Can you explain Fourth Normal Form?

(DB) Can you explain Fifth Normal Form?

(DB) What is the difference between Fourth and Fifth normal form?

(DB) Have you heard about sixth normal form?

(Q) What is Extent and Page?

(DB) What are the different sections in Page?

(Q) What are page splits?

(Q) In which files does SQL Server actually store data?

(DB) Can we have a different collation for database and table?

Chapter 1: Database Concepts

(Q) What is a Database or Database Management System (DBMS)?

Twist: What is the difference between a file and a database? Can files qualify as a database?

Note: Probably these questions are too basic for experienced SQL SERVER guys. But from a fresher’s point of view, it can be a difference between getting a job and being jobless.

Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.

Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.

Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.

Ok, let me spend a few more sentences on explaining the third aspect. Below is a simple figure of a text file that has personal detail information. The first column of the information is Name, second Address and finally Phone Number. This is a simple text file, which was designed by a programmer for a specific application.

Figure 1.1: Non-Uniform Text File

It works fine in the boundary of the application. Now, some years down the line a third party application has to be integrated with this file. In order for the third party application to be integrated properly, it has the following options:

Use the interface of the original application.

Understand the complete details of how the text file is organized, example the first column is Name, then Address and finally Phone Number. After analyzing, write a code which can read the file, parse it etc. Hmm, lot of work, right.

That’s what the main difference is between a simple file and a database; database has an independent way (SQL) of accessing information while simple files do not (That answers my twisted question defined above). File meets the storing, managing and retrieving part of a database, but not the independent way of accessing data.

Note: Many experienced programmers think that the main difference is that file cannot provide multi-user capabilities which a DBMS provides. But if you look at some old COBOL and C programs where files were the only means of storing data, you can see functionalities like locking, multi-user etc. provided very efficiently. So it’s a matter of debate. If some interviewers think of this as a main difference between files and database, accept it… going in to debate means probably losing a job.

(Just a note for fresher’s: Multi-user capabilities mean that at one moment of time more than one user should be able to add, update, view and delete data. All DBMS' provides this as in-built functionalities, but if you are storing information in files, it’s up to the application to write logic to achieve these functionalities).

(Q) What is the Difference between DBMS and RDBMS?

As mentioned before, DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. RDBMS also provides what DBMS provides, but above that, it provides relationship integrity. So in short, we can say:

RDBMS = DBMS + REFERENTIAL INTEGRITY

For example, in the above Figure 1.1, every person should have an Address. This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using “Foreign Keys” in any RDBMS.

Many DBMS companies claimed that their DBMS product was RDBMS compliant, but according to industry rules and regulations, if the DBMS fulfills the twelve CODD rules, it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.

Note: One of the biggest debates is whether Microsoft Access is an RDBMS? We will be answering this question in later section.

(DB)What are CODD Rules?

Twist: Does SQL SERVER support all the twelve CODD rules?

Note: This question can only be asked on two conditions when the interviewer is expecting you to be at a DBA job or you are complete fresher, yes and not to mention the last one he treats CODD rules as a religion. We will try to answer this question from the perspective of SQL SERVER.

In 1969, Dr. E. F. Codd laid down 12 rules, which a DBMS should adhere to in order to get the logo of a true RDBMS.

Rule 1: Information Rule

"All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables."

In SQL SERVER, all data exists in tables and are accessed only by querying the tables.

Rule 2: Guaranteed Access Rule

"Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."

In flat files, we have to parse and know the exact location of field values. But if a DBMS is truly an RDBMS, you can access the value by specifying the table name, field name, for instance Customers.Fields [‘Customer Name’].

SQL SERVER also satisfies this rule. In ADO.NET we can access field information using table name and field names.

Rule 3: Systematic Treatment of Null Values

"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.”

In SQL SERVER, if there is no data existing, NULL values are assigned to it. Note NULL values in SQL SERVER do not represent spaces, blanks or a zero value; it is a distinct representation of missing information and thus satisfies rule 3 of CODD.

Rule 4: Dynamic On-line Catalog Based on the Relational Model

"The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."

The Data Dictionary is held within the RDBMS. Thus, there is no need for off-line volumes to tell you the structure of the database.

Rule 5: Comprehensive Data Sub-language Rule

"A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items:

Data Definition

View Definition

Data Manipulation (Interactive and by program)

Integrity Constraints

Authorization

Transaction boundaries ( Begin, commit and rollback)"

SQL SERVER uses SQL to query and manipulate data, which has a well-defined syntax and is being accepted as an international standard for RDBMS.

Note: According to this rule, CODD has only mentioned that some language should be present to support it, but not necessary that it should be SQL. Before the 80’s, different’s database vendors were providing their own flavor of syntax until in 1980, ANSI-SQL came in to standardize this variation between vendors. As ANSI-SQL is quite limited, every vendor including Microsoft introduced their additional SQL syntax in addition to the support of ANSI-SQL. You can see SQL syntax varying from vendor to vendor.

Rule 6: View-updating Rule

"All views that are theoretically updatable are also updatable by the system."

In SQL SERVER, not only views can be updated by the user, but also by SQL SERVER itself.

Rule 7: High-level Insert, Update and Delete

"The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data, but also to the insertion, update and deletion of data."

SQL SERVER allows you to update views that in turn affect the base tables.

Rule 8: Physical Data Independence

"Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."

Any application program (C#, VB.NET, VB6, VC++ etc) does not need to be aware of where the SQL SERVER is physically stored or what type of protocol it is using, the database connection string encapsulates everything.

Rule 9: Logical Data Independence

"Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables."

Application programs written in C# or VB.NET do not need to know about any structure changes in SQL SERVER database. Example: adding of new field etc.

Rule 10: Integrity Independence

"Integrity constraints specific to a particular relational database must be definable in the relational data sub-language and storable in the catalog, not in the application programs."

In SQL SERVER, you can specify data types (integer, nvarchar, Boolean etc.) which put in data type checks in SQL SERVER rather than through application programs.

Rule 11: Distribution Independence

"A relational DBMS has distribution independence."

SQL SERVER can spread across more than one physical computer and across several networks; but from application programs, it has not a big difference but just specifying the SQL SERVER name and the computer on which it is located.

Rule 12: Non-subversion Rule

"If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."

In SQL SERVER whatever integrity rules are applied on every record are also applicable when you process a group of records using application program in any other language (example: C#, VB.NET, J# etc.).

Readers can see from the above explanation that SQL SERVER satisfies all the CODD rules, some database gurus consider SQL SERVER as not truly being an RDBMS, but that’s a matter of debate.

(Q) Is Access Database a RDBMS?

Access fulfills all rules of CODD, so from this point of view, yes it’s truly an RDBMS. However, many people can contradict it as a large community of Microsoft professionals think that Access is not an RDBMS.

(Q) What is the Main Difference between ACCESS and SQL SERVER?

As mentioned before, Access fulfills all the CODD rules and behaves as a true RDBMS. But there’s a huge difference from an architecture perspective, due to which many developers prefer to use SQL SERVER as the major database rather than Access. Following is the list of architecture differences between them:

Access uses file server design and SQL SERVER uses the Client / Server model. This forms the major difference between SQL SERVER and ACCESS.
Note: Just to clarify what is client server and file server I will make a quick description of widely accepted architectures. There are three types of architectures:

Main frame architecture (This is not related to the above explanation but just mentioned as it can be useful during an interview and also for comparing with other architectures)

File sharing architecture (Followed by ACCESS)

Client Server architecture (Followed by SQL SERVER).

In Main Frame architecture, all the processing happens on central host server. User interacts through a dumb terminal that only sends keystrokes and information to the host. All the main processing happens on the central host server. So the advantage in such type of architecture is that you need least configuration clients. But the disadvantage is that you need a robust central host server like Main Frames.

In File sharing architecture, which is followed by Access database, all the data is sent to the client terminal and then processed. For instance, if you want to see customers who stay in India, in File Sharing architecture all customer records will be sent to the client PC regardless whether the customer belongs to India or not. On the client PC customer records from India are sorted/filtered out and displayed, in short all processing logic happens on the client PC. Therefore, in this architecture, the client PC should have heavy configuration and it increases network traffic as a lot of data is sent to the client PC. However, the advantage of this architecture is that your server can be of a low configuration.

Figure 1.2: File Server Architecture of Access

In client server architecture, the above limitation of the file server architecture is removed. In client server architecture, you have two entities, client and the database server. File server is now replaced by database server. Database server takes up the load of processing any database related activity and the client does any validation aspect of database. As the work is distributed between the entities it increases scalability and reliability. Second, the network traffic also comes down as compared to file server. For example if you are requesting customers from India, database server will sort/ filter and send only Indian customer details to the client, thus bringing down the network traffic tremendously. SQL SERVER follows the client-server architecture.

Figure 1.3: Client Server Architecture of SQL SERVER

The second issue comes in terms of reliability. In Access, the client directly interacts with the Access file, in case there is some problem in the middle of a transaction, there are chances that an Access file can get corrupt. But in SQL SERVER, the engine sits in between the client and the database, so in case of any problems in the middle of a transaction, it can revert back to its original state.
Note: SQL SERVER maintains a transaction log by which you can revert back to your original state in case of any crash.

When your application has to cater to a huge load demand, highly transactional environment and high concurrency, then its better to go for SQL SERVER or MSDE.

But when it comes to cost and support, Access stands better than SQL SERVER. In case of SQL SERVER, you have to pay for per client license, but Access runtime is free.

Summarizing: SQL SERVER gains points in terms of network traffic, reliability and scalability whereas Access gains points in terms of cost factor.

(Q) What is the Difference between MSDE and SQL SERVER 2000?

MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER database. It is primarily provided as a low cost option for developers who need a database server, which can easily be shipped and installed. It can serve as a good alternative for Microsoft Access database as it overcomes quite a few problems which Access has.

Below is a complete list, which can give you a good idea of the differences:

Size of database: Microsoft Access and MSDE have a limitation of 2GB while SQL SERVER has 1,048,516 TB1.

Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or is equal to 8. It does not mean that you cannot have more than eight concurrent operations but the performance degrades. Eight-connection performance degradation is implemented by using SQL SERVER 2000 workload governor (we will be looking into more detail of how it works). As compared to SQL SERVER 2000, you can have 32,767 concurrent connections.

MSDE does not provide OLAP and Data warehousing capabilities.

MSDE does not have support facility for SQL mail.

MSDE 2000 does not have GUI administrative tool such as enterprise manager, Query analyzer or Profiler. But there are roundabout ways by which you can manage MSDE 2000:

Old command line utility OSQL.EXE

VS.NET IDE Server Explorer: Inside VS.NET IDE, you have a functionality which can give you a nice GUI administrative tool to manage IDE.

SQL SERVER WEB Data administrator installs a web based GUI which you can use to manage your database.
For any details refer here.

SQL-DMO objects can be used to build your custom UI

There are many third party tools, which provide administrative capability GUI, which is out of scope of the book as it is only meant for interview questions.

MSDE does not support Full text search.

Summarizing: There are two major differences: The first is the size limitation (2 GB) of the database and second is the concurrent connections (eight concurrent connections) which are limited by using the workload governor. During an interview, this answer will suffice if the interviewer is really testing your knowledge.

(Q) What is SQL SERVER Express 2005 Edition?

Twist: What is the difference between SQL SERVER Express 2005 and MSDE 2000?

Note: Normally comparison is when the product is migrating from one version to other version. When SQL SERVER 7.0 was migrating to SQL 2000, asking differences was one of the favorite questions.

SQL SERVER Express edition is a scaled down version of SQL SERVER 2005 and the next evolution of MSDE.

Listed below are some major differences between them:

MSDE maximum database size is 2GB while SQL SERVER Express has around 4GB.

In terms of programming language support MSDE has only TSQL, but SQLSERVER Express has TSQL and .NET. In SQL SERVER Express 2005, you can write your stored procedures using .NET.

SQL SERVER Express does not have connection limitation, which MSDE had and was controlled through the workload governor.

There was no XCOPY support for MSDE, SQL SERVER Express has it.

DTS is not present in SQL SERVER express while MSDE has it.

SQL SERVER Express has reporting services while MSDE does not.

SQL SERVER Express has native XML support and MSDE does not.

Note: Native XML support means now in SQL SERVER 2005:

You can create a field with data type XML.

You can provide SCHEMA to the SQL SERVER fields with XML data type.

You can use new XML manipulation techniques like XQUERY also called as XML QUERY.

There is a complete chapter on SQL SERVER XML Support, so till then this will suffice.

Summarizing: The major difference is the database size (2 GB and 4 GB), support of .NET support in stored procedures and native support for XML. This much can convince the interviewer that you are clear about the differences.

(DB) What is SQL Server 2000 Workload Governor?

Workload governor limits the performance of SQL SERVER Desktop engine (MSDE) if the SQL engine receives more load than what is meant for MSDE. MSDE was always meant for trial purpose and non-critical projects. Microsoft always wanted companies to buy their full blow version of SQL SERVER, so in order that they can put limitation on MSDE performance and number of connections, they introduced Workload governor.

Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine.

Note: It does not limit the number of connections but makes the connection request go slow. By default 32,767 connections are allowed both for SQL SERVER and MSDE. But it just makes the database engine go slow above eight connections.

What is the Difference between SQL SERVER 2000 and 2005?

Twist: What is the difference between Yukon and SQL SERVER 2000?

Note: This question will be one of the favorites during SQL SERVER interviews. I have marked the points which should be mentioned by developers as PG and DBA for Database Administrator.

Following are some major differences between the two versions:

(PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.

(PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.

(PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved using varbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers used varchar or text data type and all validation had to be done programmatically.

(PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.

(PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.

For huge databases, SQLSERVER has provided a cool feature called “Data partitioning”. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, “partitioning” is transparent.

In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.

Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program’s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.

In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.

SERVER 2005 has support of “Multiple Active Result Sets” also called as “MARS”. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.

In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it’s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.

This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 – support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.

SMO will be used for SQL Server Management.

AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).

Replication is now managed by RMO (Replication Management Objects).
Note: SMO, AMO and RMO are all using .NET Framework.

SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
Note: There is a question on this later see for execution context questions.

In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
Note: There are questions on this, refer “Schema” later.
Note: Ok below are some GUI changes.

Query analyzer is now replaced by query editor.

Business Intelligence development studio will be used to create Business intelligence solutions.

OSQL and ISQL command line utility is replaced by SQLCMD utility.

SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.

SERVER Manager which was running in system tray is now replaced by SQL Computer manager.

Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.

In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.

(PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level “Snapshot Isolation level”.
Note: We will see “Snapshot Isolation level” in detail in the coming questions.

Summarizing: The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.

(Q) What are E-R diagrams?

E-R diagram also termed as Entity-Relationship diagram shows the relationship between various tables in the database. Example: Tables Customer and Customer Addresses have a one to many relationship (i.e. one customer can have multiple addresses) this can be shown using the ER diagram. ER diagrams are drawn during the initial stages of a project to forecast how the database structure will shape up. Below is a screen shot of a sample ER diagram of “Asset Management” which ships free with Access.

Figure 1.4: Asset management ER diagram.

(Q) How many Types of Relationship Exist in Database Designing?

There are three major relationship models:

One-to-one

Figure 1.5: One-to-One relationship ER diagram

One-to-many
In this many records in one table correspond to the one record in another table.
Example: Every one customer can have multiple sales. So there exist one-to-many relationships between customer and sales table.
One Asset can have multiple Maintenance. So Asset entity has one-to-many relationship between them as the ER model shows below.

Figure 1.6: One-to-Many Relationship ER diagram

Many-to-many
In this, one record in one table corresponds to many rows in another table and also vice-versa.
For instance: In a company, one employee can have many skills like Java , C# etc. and also one skill can belong to many employees.
Given below is a sample of many-to-many relationship. One employee can have knowledge of multiple Technology. So in order to implement this, we have one more table Employee Technology which is linked to the primary key of Employee and Technology table.

Figure 1.7: Many-to-Many Relationship ER diagram

(Q) What is Normalization? What are the Different Types of Normalization?

Note: A regular .NET programmer working on projects often stumbles on this question, which is but obvious. The bad part is sometimes the interviewer can take this as a very basic question to be answered and it can be a turning point for the interview. So let's cram it.

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

Avoiding repetitive entries

Reducing required storage space

Preventing the need to restructure existing tables to accommodate new data

Increased speed and flexibility of queries, sorts, and summaries

Note: During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.

The three normal forms as follows:

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.

Figure 1.8: Repeating groups example

In the above example, city1 and city2 are repeating. In order for these tables to be in First normal form, you have to modify the table structure as follows. Also note that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to the smallest unit).

Figure 1.9: Customer table normalized to first normal form

Second Normal Form

The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. In other words, each non-key field should be a fact about all the fields in the primary key.

In the above table of customer, city is not linked to any primary field.

Figure 1.10: Normalized customer table.

Figure 1.11: City is now shifted to a different master table.

That takes our database to a second normal form.

Third Normal Form

A non-key field should not depend on another Non-key field. The field Total is dependent on Unit price and qty.

Figure 1.12: Fill third normal form

So now the Total field is removed and is the multiplication of Unit price * Qty.

(Q) What is Denormalization?

Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

(DB) Can you Explain Fourth Normal Form?

Note: Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.

In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy “Third Normal form”.

So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as “multi-valued facts”.

Figure 1.13: Multi-valued facts

In the above table, you can see that there are two many-to-many relationships between Supplier / Product and “Supplier / Location (or in short multi-valued facts). In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables.

Figure 1.14: Normalized to Fourth Normal form.

(DB) Can you Explain Fifth Normal Form?

Note: UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?

Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell the Product should be registered with the Company. So these three entities have a mutual relationship within them.

Figure 1.15: Not in Fifth Normal Form.

The above table shows some sample data. If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:

JM Associate should be an authorized dealer of Cadbury

Sweets should be manufactured by Cadbury company

These two smaller bits of information form one record of the above given table. So in order for the above information to be “Fifth Normal Form” all the smaller information should be in three different places. Below is the complete fifth normal form of the database.

Figure 1.16: Complete Fifth Normal Form

(DB) What is the Difference between Fourth and Fifth normal form?

Note: There is a huge similarity between Fourth and Fifth normal form, i.e. they address the problem of “Multi-Valued facts”.

“Fifth normal form” multi-valued facts are interlinked and “Fourth normal form” values are independent. For instance in the above two questions Supplier/Product and Supplier/Location are not linked. While in fifth form, the Dealer/Product/Companies are completely linked.

(DB) Have you Heard about Sixth Normal Form?

Note: Arrrrggghhh yes there exists a sixth normal form also. But note guys you can skip this statement. Just in case you want to impress the interviewer...

If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly.

(Q) What is Extent and Page?

Twist: What is the relationship between Extent and Page?

Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed, it allocates a new extent with data pages.

While extent is basic unit storage from a database point of view, page is a unit of allocation within extent.

(DB) What are the Different Sections in Page?

Page has three important sections:

Page header

Actual data i.e. Data row

Row pointers or Row offset

Page header has information like timestamp, next page number, previous page number etc.

Data rows are where your actual row data is stored. For every data row, there is a row offset which points to that data row.

Figure 1.17: General view of a Extent

(Q) What are Page Splits?

Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as “Page Split”.

(Q) In which Files does SQL Server Actually Store Data?

Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.

Figure 1.18: MDF and LDF files.

(Q) What is Collation in SQL Server?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Figure 1.19: Collation according to language

Note: Different languages will have different sort orders.

Case Sensitivity

If A and a, B and b, etc. are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent Sensitivity

If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

(DB) Can We Have a Different Collation for Database and Table?

Yes, you can specify different collation sequence for both the entities differently.

what is .NET RIA service

2009-07-16T13:52:00.002+05:30

Microsoft .NET RIA Services simplifies the traditional n-tier application pattern by bringing together the ASP.NET and Silverlight platforms. The RIA Services provides a pattern to write application logic that runs on the mid-tier and controls access to data for queries, changes and custom operations. It also provides end-to-end support for common tasks such as data validation, authentication and roles by integrating with Silverlight components on the client and ASP.NET on the mid-tier.

Get more information here

capabilities of Silverlight

2009-07-16T13:44:00.004+05:30

offline capabilities

Silverlight 2 doesn’t support offline scenario out of box. While you can run an html file that contains a Silverlight application from the local file system, there are several restrictions:

* Cannot access resources in the local file system other than the folder and subfolders the hosting html file is in.

* Cannot use any web related features, such as calling web service.

* Cannot access online medias such as images and videos hosted in any web site.

Silverlight 3 will add more offline capabilities, such as the out of browser feature.

Another interesting scenario is occasionally connected environment. In such an environment, your application supports both online and offline features. When the internet connection works, you can get the latest data from the internet. When the internet connection is lost, you can still work with data that has been downloaded to the local device. You can modify the data in the local device, and once the internet connection is restored, the local data can be synched to the cloud.

Silverlight doesn’t offer this capability out of box, but you can use Live Services. It allows you to run a Silverlight application out of the browser, and yet be able to call web services since it’s still in the http protocol. It also supports the above occasionally connected scenario without much effort from your part. The data will be stored in cloud and synched among every device the user owns, such as the Live Desktop in the cloud, a PC, a Mac, and a mobile device.

Does Silverlight support web camera and printing?

Unfortunately, Silverlight doesn’t support web camera yet. You can print the containing web page, which will include the Silverlight application’s content if it is currently visible. Features such as print areas beyond the visible areas in a ScrollViewer are not supported yet.

ASP.NET and SILVERLIGHT in action

2009-07-16T13:35:00.004+05:30

A common question, when to use Silverlight, ASP.NET or both. This depends on different scenarios. Here are some common approaches:

Pure Silverlight.
One approach is to completely remove ASP.NET, this solution works best if you’re working on a new development. You only need to work on Silverlight, without any worry about integration problems. If you need to communicate with the server, you write web services, such as WCF. It will also help you when you need to port part of or the whole of your system to other hostings or even other platforms, since the client and the server are completely separate.

ASP.NET plus a Silverlight island.
This approach is generally used when the Silverlight content and the ASP.NET content have little relationship. For example, an ASP.NET blog engine with a Silverlight media player in a blog post. This approach is very easy to implement, and allows you to reach the broadest audience. For example, if a user hasn’t installed Silverlight, he can still read the blog posts, but he can’t watch the videos.

Use ASP.NET AJAX instead of Silverlight
ASP.NET AJAX is designed to work with ASP.NET. It is mainly an extension to ASP.NET. While AJAX can’t provide you the advance user experience that Silverlight can, for many scenarios, it should be sufficient. This approach also helps if you have strong ASP.NET experience, but are still quite new to Silverlight.

Within this approach, there are two branches. One is to mix the client and server code by using UpdatePanel, AJAX Control Toolkit, and etc. The other method is to take the pure AJAX approach, where you write html and JavaScript instead of using Server Controls, and call web services to communicate with the server. The former branch is easier to implement, especially if you have strong ASP.NET experience, but lack JavaScript knowledge. The latter branch proves to be better in architecture when you want to port the AJAX application to other technologies such as Silverlight. Especially since you only need to rewrite the client side code, and can keep the web services as they are. The programming model for the later branch is similar to Silverlight. Therefore, this approach is rarely taken if you’re experienced in Silverlight.

Silverlight with ASP.NET.
More often, you may want to port an existing ASP.NET application to Silverlight, but you don’t want to completely rewrite the entire application. This is the most difficult approach since you’re mixing client side and server side technologies.

Before going with this approach, please consider if the above approaches can solve your problem. Ask yourself the following questions:

1. Do you really need a rich interactive user experience?

This is normally a requirement for consumer oriented applications, but for most business applications, you only need a “good” user experience, which AJAX is sufficient to provide.

2. Can you add Silverlight islands to your existing ASP.NET application instead of mixing the contents?

This should work for most scenarios. For example, Windows Live Mail is built in ASP.NET, with a few Silverlight islands, such as a slide show program that allows you to view photo attachments with enhanced experience (actually most Microsoft created web applications takes this approach).

3. Will this be a good chance to revise your architecture?

Most traditional ASP.NET applications use the B/S or three tire architecture, in which the application works with a database either directly, or through a business logic layer. When porting applications to other platforms, these architectures will introduce many problems. When investigating Silverlight, it is also a good chance to adopt SOA. Add a service facade layer on top of the business logic layer, and you can work with the services from almost any client, such as an ASP.NET application and a Silverlight application. If you are already on SOA, it should be trivial to port to Silverlight, since you only need to rewrite a client application. With SOA, the ASP.NET AJAX approach and the Silverlight island approach will also be much easier to implement.

If none of the above approaches is suitable, you may have to mix Silverlight content with ASP.NET. When using this approach, keep in mind that Silverlight can’t call ASP.NET server side event handlers, and each post back (either partial or complete) will cause the Silverlight application to reload.

ASP.NET vs SILVERLIGHT

2009-07-16T13:27:00.002+05:30

Technically, Silverlight and ASP.NET are completely different. The major differences are as listed below:

* Silverlight runs completely on the client, while ASP.NET mainly runs on the server, but also has a few parts running on the client.

* When an event fires, Silverlight handles the event on the client, but in ASP.NET, the browser will make an HTTP POST to the server, and after the server handles the request, it sends a new html page to the client.

* A Silverlight application sends rendering commands to the Silverlight rendering engine by either write XAML or code, and the Silverlight rendering engine will handle the rendering task. On the other hand, ASP.NET doesn’t have a rendering engine. It generates an html file on the server, sends it to the client, and allows the browser to parse the html file and render the content.

* Silverlight can’t work with a database directly, instead it consumes data from web services, while ASP.NET has strong support in working with database directly.

The most important fact to remember is what runs on the client, and what runs on the server, since this will affect almost every part of your system. Silverlight runs on the client, and ASP.NET runs on the server. They can work together and are compatible with each other, but this requires some time and effort.