With Clause In Sql Server

Before learning about "With" clause and it's usage ,you must know some thing about "Common Table Expression"

Before Sql Server 2005 ,there is no concept of "With" Clause and now may get a doubt that then what is the use of it from Sql server 2005 .In order to simplify your work with temporary tables the Microsoft has introduced "With" Clause from Sql server 2005,but it is not the replacement of temporary tables .

In SQL, Common Table Expression (CTE) is created using the WITH statement followed by common table expression (CTE) name.

What is Common Table Expression?

A Common Table Expression is an expression that returns a temporary result set from the Select statement which we written in "With clause" .

What is Syntax of Common Table Expression?

WITH  Common _Table_expression_name &nbsp [ ( column_name [,...n] ) ] AS ( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query
definition.

A very basic, self-explanatory example:

WITH Dept(Deptid, DeptName) AS ( SELECT Deptid, DeptName FROM Department ) SELECT EmpName ,DeptName FROM Employe Emp join Dept on Emp.deptid= Dept.Deptid

If a Temporary Table is used, first we have to be create, and then use it and it could be called over and over again
but a Common Table Expression must be used immediately after creating it.

With clause helps in providing the re-usability of the code and gives good performance. There are cases where we use the same piece of sub sql query multiple times in complex query. In such cases we can use the with clause and improve the performance of the query. The with clause is just like a table but it stores the data in a temporary location. This can be used in the main query.

An Intrduction to Database Management Systems

What is Database ?

A database is a collection of related files that are usually integrated, linked or cross-referenced to one another. The advantage of a database is that data and records contained in different files can be easily organized and retrieved using specialized database management software called a database management system (DBMS) or database manager.

After reading this lesson, you should be able to:

• Define the term database management system (DBMS).
• Describe the basic purpose and functions of a DBMS.
• Discuss the advantages and disadvantages of DBMSs.

DBMS Fundamentals

A database management system is a set of software programs that allows users to create, edit and update data in database files, and store and retrieve data from those database files. Data in a database can be added, deleted, changed, sorted or searched all using a DBMS. If you were an employee in a large organization, the information about you would likely be stored in different files that are linked together. One file about you would pertain to your skills and abilities, another file to your income tax status, another to your home and office address and telephone number, and another to your annual performance ratings. By cross-referencing these files, someone could change a person's address in one file and it would automatically be reflected in all the other files. DBMSs are commonly used to manage:

• Membership and subscription mailing lists
• Accounting and bookkeeping information
• The data obtained from scientific research
• Customer information
• Inventory information
• Personal records
• Library information

DBMSs and File Management Systems

Computerized file management systems (sometimes called file managers) are not considered true database management systems because files cannot be easily linked to each other. However, they can serve as useful data management functions by providing a system for storing information in files. For example, a file management system might be used to store a mailing list or a personal address book. When files need to be linked, a relational database should be created using database application software such as Oracle, Microsoft Access, IBM DB2, or FileMaker Pro.

The Advantages of a DBMS

Improved availability: One of the principle advantages of a DBMS is that the same information can be made available to different users.

Minimized redundancy: The data in a DBMS is more concise because, as a general rule, the information in it appears just once. This reduces data redundancy, or in other words, the need to repeat the same data over and over again. Minimizing redundancy can therefore significantly reduce the cost of storing information on hard drives and other storage devices. In contrast, data fields are commonly repeated in multiple files when a file management system is used.

Accuracy: Accurate, consistent, and up-to-date data is a sign of data integrity. DBMSs foster data integrity because updates and changes to the data only have to be made in one place. The chances of making a mistake are higher if you are required to change the same data in several different places than if you only have to make the change in one place.

Program and file consistency: Using a database management system, file formats and system programs are standardized. This makes the data files easier to maintain because the same rules and guidelines apply across all types of data. The level of consistency across files and programs also makes it easier to manage data when multiple programmers are involved.

User-friendly: Data is easier to access and manipulate with a DBMS than without it. In most cases, DBMSs also reduce the reliance of individual users on computer specialists to meet their data needs.

Improved security: As stated earlier, DBMSs allow multiple users to access the same data resources. This capability is generally viewed as a benefit, but there are potential risks for the organization. Some sources of information should be protected or secured and only viewed by select individuals. Through the use of passwords, database management systems can be used to restrict data access to only those who should see it.

The Disadvantages of a DBMS

There are basically two major downsides to using DBMSs. One of these is cost, and the other the threat to data security.

Cost: Implementing a DBMS system can be expensive and time-consuming, especially in large organizations. Training requirements alone can be quite costly.

Security: Even with safeguards in place, it may be possible for some unauthorized users to access the database. In general, database access is an all or nothing proposition. Once an unauthorized user gets into the database, they have access to all the files, not just a few. Depending on the nature of the data involved, these breaches in security can also pose a threat to individual privacy. Steps should also be taken to regularly make backup copies of the database files and store them because of the possibility of fires and earthquakes that might destroy the system.

Different Types Of Tables In Sql Server

How to create permanent table in MicroSoft SQL Server database?

CREATE TABLE dbo.Table_Name
(
Column_Name1 DATATYPE,
Column_Name2 DATATYPE)
)

How to create temporary table in MicroSoft SQL Server database?

In Microsoft SQL Server database, temporary tables are available in two types. Based on requirement user can create either one.
Following are two types of temporary tables available in Microsoft SQL Server database.

• Local Temporary Table
• Global Temporary Table

 Local Temporary Table
How to create Local temporary table object in Microsoft SQL Server Database?
Using single pound sign (#) along with the name of the table, Local temporary table can be created. As shown in below example how to create a local temporary table.

Example For Temporary Table:

CREATE TABLE #emp_hist
(
empno INT,
empname VARCHAR(40)
)