Republish Article: Sql: Coalesce() Function

Sql: Coalesce() Function

Author: Programminghelp.com

Please continue at Programminghelp.com Thanks and happy coding!

When developing queries in SQL Server, dealing with nulls can often be challenging. The COALESCE function was included in SQL 2005 to help deal with these issues. First let’s take a look at what COALESCE can offer. Generally, COALESCE function receives a sequence of values and a default value to used when all of items in the sequence of values are null. From here the function returns the first not-null in the sequence of values list.

Scenario

There is a requirement of showing a user’s full name and how much money they get paid per week. This scenario will be divided up into two segments, displaying the user’s full name and then computing and showing their weekly earnings. Please feel free to use the attached database to follow along.

Implementation

In this first example, suppose there is a table of users that have the columns FirstName, MiddleName and LastName. The table holds the following values:

1


In many applications, the requirement of welcoming the user is often needed. So, to put the user’s full name on the screen, a stored procedure using the COALESC function properly format all three fields into one field. So the query using COALESCE looks like this:

 

SELECT (FirstName + ' ' + COALESCE(MiddleName,'') + ' ' +
COALESCE(LastName,'')) AS FullName
FROM Users

The results will look like this:

2

For the next example, let’s extend the previous example by adding some information on how much the users get paid per week. Some users get paid by the hour while others get paid on a salary with a commission, so the table reflects this information.

For this example the table looks like this:

 

 

Please continue at Programminghelp.com Thanks and happy coding!

Article Source: http://www.articlesbase.com/databases-articles/sql-coalesce-function-521838.html

About the Author

Programming Help is a community collaboration for programming resources, tutorials, articles, forums, and help. Offering articles written by programmers and developers with years of experience. ProgrammingHelp.com strives to develop and create context and articles in which the beginning and most advanced level programmers/coders can use in everyday methods of development. We welcome you to come join us with your questions and knowledge to help us create an excelled programming community and open forum.

Republish Article: Query optimization

Query optimization

Author: rathish

Sooner or later SQL query performance becomes an issue, for any production database. Long-running queries may lead to table locking and data corruption issues, alongwith consuming system resources causing the server and application to run slowly. So, query optimization is important.

Guiding principles for query optimization:

1. Understand how your query is being executed by your database is executing your query

Nowadays all databases offer a way to understand how a query is executed. The first step for query optimization is to understand what the database is doing. Different databases provide different means to understand this. Syntax for MySQL, "EXPLAIN [SQL Query]"; In Oracle, "EXPLAIN PLAN FOR [SQL Query]".

2. Retrieve as little data as possible

The resources that are required to execute a query depends upon the size of data that is to be retrieved. Instead of using 'SELECT *' for retrieving values from a table, use SELECT with the column name whose value u are interested in viewing. The query would then execute faster.

3. Store intermediate results

Using views,unions and subqueries for simplifying execution is a common technique among DBA's. In order to execute a query, the results are immediately used within the query. However when the amount of data retrieved is large it may cause performance issues.

For large data one can use tables to temporarily store values, or break up SQL statements to efficiently execute it. To speed up the query performance even more, one can make use of index on the table.

Below are several specific query optimization strategies.

* Use Index
One of the quick and easy way to speed up a query processing is to make use of index.

* Aggregate Table
Pre-populating tables at higher levels so less amount of data need to be parsed.

* Vertical Partitioning
Partition the table by columns. The strategy helps reduce the amount of data a SQL query needs to process.

* Horizontal Partitioning
Partition the table by data value, most often time.

* Denormalization
The process of denormalization speeds up performance, because it combines multiple tables into a single table. Thus fewer table joins are needed.

* Server Tuning
Each server has its own parameters, and often tuning server parameters
to take full advantage of the hardware resources can significantly speed up query performance.

Article Source: http://www.articlesbase.com/databases-articles/query-optimization-3111199.html

About the Author

Rathish Nair,

Visit ResourceMasti now:

http://resourcemasti.blogspot.com/2010/07/query-optimization.html

Republish Article: Benefits of Data Base software

Benefits of Data Base software

Author: Talha Bin Shakeel

In its most general sense, a database is any electronically stores collection of data in a computer system. In its more specific sense, a database is a collection of interrelated files in a computer system. These computer-based files are organized according to their common elements, so that they can be retrieved easily. Sometimes called a database manager or database management systems (DBMS), database software is a program that sets up and controls the structure of a database and access to the data.

More details on data base will be covered in the next article, as here we are about to discuss the Benefits of Database Software.

 

The Benefits Of Data Base Software:

When data is stored in separate files, the same data will be repeated in many files. In the old days, each college administrative office—registrar, financial aid, housing and so on—might have a separate file on you. Thus, there was redundancy – your address, for example, was repeated over and over. The advantage of database software is that data is not in separate files. Rather it is integerated. Thus, your address need only be listed once, and all the separate administrative offices will have access to the same information. For that reason, information in database is considered to have more integrity. That is, the information is more likely to be accurate and up to date.

 

Databases are a lot more interesting than they used to be. Once they included only text. Now they can include pictures, sounds, and animation. It's likely, for instance, that your personnel record in a future company database will include a picture of you and perhaps even a clip of you voice. If you go looking for a house to buy, you will be able to view a real estate agent's database of video clips of homes and properties without leaving the realtor's office.

Today the principal microcomputer database programs are Microsoft Access, Corel Paradox and Lotus approach. (In larger systems, Oracle is a major player, and is very easy to use) These programs also allow users to attach multimedia – sound, motion, and graphics – to forms.

 

The advantages of DBMS ( database management systems) are as follows:

1) File sharing

2) Reduced data redundancy

3) Improved Data Integrity

4) Increased Security

 

Databases can be classified into four different types as follows

1) Individual database

2) Shared Database

3) Distributed Database

4) Public Database

 

The Ways Database Are Organized:

The four most common arrangements are hierarchical, network, relational and object oriented.

 

I will tell you more About all the advantages and types in the next posting, soon.

Article Source: http://www.articlesbase.com/databases-articles/benefits-of-data-base-software-3179575.html

About the Author

Talha has been writing articles for nearly 1 year. Come visit his latest website over at http://www.scentedsoycandles.co.cc which helps people find the best scented soy candles and information they are looking for when planning for some decoration and to give away great gifts or to stimulate romance.

Republish Article: Sql Server Integration Services: Calling Ssis Package in C#

Sql Server Integration Services: Calling Ssis Package in C#

Author: Programminghelp.com

 

SQL 2005: SQL Server Integration Services
Calling SSIS Package from C# Code

 

Introduction

During this series about SQL Server Integration Services, the basics of importing, exporting, manipulating data, typical database maintenance and deployment of SSIS packages have been covered. In this last article of this introductory series on SSIS, the technique of calling a SSIS package from C# will be covered.

Scenario

As seen in the last article, a package was successfully deployed and ran in SQL Server Management Studio. But there may be times when a package needs to be run by a user initiated action. This could be from another application or web service that has access to the where the dtsx package is physically located. For this example, a simple console application will be used to show how to call a SSIS package from some C# code.

Implementation

Open up Visual Studio and create a C# Console Project. Once the project is loaded, the first thing to take place is to add the reference to the code that uses SSIS packages. In solution explorer, add a reference to the Micorsoft.SQLServer.Managed.DTS.dll. Then in the blank class file add a using statement to reference the dll. There are four variable that are needed to be able to run a dtsx package. One being a string variable where the physical location of the package located, hence pkgLocation. Next is the actual package object, ssisPackage, followed by the application running the package, ssisApp. Finally it would be nice to display the results of the package once it is executed so ssisPackageResults will be used.

Please Visit Programminghelp.com For the full article and Pictorial tutorial.

http://www.programminghelp.com/database/sqlserver/sql-server-integration-services-calling-ssis-package-in-c/

 

Article Source: http://www.articlesbase.com/databases-articles/sql-server-integration-services-calling-ssis-package-in-c-514611.html

About the Author

Programming Help is a community collaboration for programming resources, tutorials, articles, forums, and help. Offering articles written by programmers and developers with years of experience. ProgrammingHelp.com strives to develop and create context and articles in which the beginning and most advanced level programmers/coders can use in everyday methods of development. We welcome you to come join us with your questions and knowledge to help us create an excelled programming community and open forum.