SQL

Indexes Nice explanation :

  

How to convert a Table Data in to XML format in SQL Server?

Select * from Customer For XML Path

Initial sql query steps

For SQL learners who are in initial state, here I am giving a small review about the query.

General structure of a query consists of mainly 3 parts

1.       Select

2.       From

3.       Where

1.       Select : This part of query consists database fields which you want  from the database  

Ex: if you want to get employee number and name from the database

                                        Select employee number, name

2.       From : Refers to the table name from where you want to get data

                               From EMP

           3.   Where: where will followed by the condition.

                            Actually, a query is just like how we are speaking

                 Ex: Qes: Display  list of employees whose name is anil ?

                                     Select   empno, address

                                      From   employee

                                     Where name=’anil’;
above query displays all employees list with the name 'anil' (if 10 employees are in the company with the name "anil" , It will display all 10 records.  )

In KEYWORD 

Suppose imagine that you have a table in the database with the column names empno,empname,empsal . Now your requirement is to display  the details of  all employers with the name  ANIL,SIVA,SAI  ,to get these records following query is help for you .                                                                                           

select * from employee where empname in ('ANIL','SIVA','SAI');        

Above query display the empno,empname,empsal details of employees whose name is among ANIL,SIVA,SAI .                                                                                                                                         If there exist 2 employees with the name ANIL then it will display both the  records.

empno       empname     empsal

1                  ANIL        15000

2                 SIVA          15000

3                 SAI             14000

4                 ANIL          12000

LIKE KEYWORD

 Here  we will discuss about the like keyword.
where it helps : Like keyword is very much helpfull in the place where we don't know the full name i.e we know only first letter of it or last letter of it or some part of the name.even if we use complete name also it will give the out put.
ex : Actually i want to search the employee ,whose name is 'ANIL' but unfortunately i forgot his  name,I remembered only his name start with 'A' this is enough for me search 'ANIL' record from database.
                                    
                        select ename from emp where ename like('A%') 
above query displays all list of employees with the name starting with the letter  'A' 


Following are list of query's based on the Like key word:
Use select required fields form table name in front  of the following query
 
WHERE FirstName LIKE '_im' finds all three-letter first names that end with 'im' (e.g. aim, sim).
 

• WHERE LastName LIKE '%iva' finds all employees whose last name ends with 'siva'
• WHERE LastName LIKE '%iva%' finds all employees whose last name includes 'iva' anywhere in the name.
• WHERE FirstName LIKE '[sh]iva' finds four-letter first names that end with 'iva' and begin with either 'J' or 'T' (that is, only Jim and Tim)
• WHERE LastName LIKE 'm[^c]%' finds all last names beginning with 'm' where the following (second) letter is not 'c'.

·         SELECT EmployeeID, FirstName, LastName, City FROM Employees

WHERE (FirstName NOT LIKE 'M%') AND (FirstName NOT LIKE 'A%')·      

   SELECT EmployeeID, FirstName, LastName,City FROM Employees

ORDER BY City

SELECT EmployeeID,FirstName,LastName,Country FROM Employees

ORDER BY Country ASC, City DESC

Desc 

DESC has two purposes. It is used to describe a schema  as well as to retrieve rows from table in descending order.
Explanation :
The query  SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.


DESC EMPLOYEE  will describe the structure of a table (not for sql). 

How to get the server time through query ?

Through query we are able to get the date string from the database. 

Following query will help you:

select convert(varchar(20),getdate(),103);

OUT PUT: 

            04/01/2011

select convert(varchar(20),getdate(),104);

OUT PUT:

           04.01.2011

select convert(varchar(20),getdate(),105)

OUT PUT:

          04-01-2011

select convert(varchar(20),getdate(),106)

OUT PUT:

          04 Jan 2011  

BY changing the number you can get different type of date formats.........! 

To display US while occurrence of 0 and  Foreign at 1

SELECT W.sno, W.userName, 
                CASE W.status
                    WHEN 1 then 'U.S.'
                    WHEN 2 then 'Foreign'
                end as Type           
                
                from wla_user_requests W

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN

This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching returns NULL values.

FULL OUTER JOIN

This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*

FROM Table1 t1

WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)

GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.

NOT INNER JOIN

Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.

USE AdventureWorks

GO

CREATE TABLE table1

(ID INT, Value VARCHAR(10))

INSERT INTO Table1 (ID, Value)

SELECT 1,'First'

UNION ALL

SELECT 2,'Second'

UNION ALL

SELECT 3,'Third'

UNION ALL

SELECT 4,'Fourth'

UNION ALL

SELECT 5,'Fifth'

GO

CREATE TABLE table2

(ID INT, Value VARCHAR(10))

INSERT INTO Table2 (ID, Value)

SELECT 1,'First'

UNION ALL

SELECT 2,'Second'

UNION ALL

SELECT 3,'Third'

UNION ALL

SELECT 6,'Sixth'

UNION ALL

SELECT 7,'Seventh'

UNION ALL

SELECT 8,'Eighth'

GO

SELECT *

FROM Table1

SELECT *

FROM Table2

GO

USE AdventureWorks

GO

/* INNER JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

INNER JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* LEFT JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* RIGHT JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* OUTER JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

GO

/* LEFT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

LEFT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t2.ID IS NULL

GO

/* RIGHT JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t1.ID IS NULL

GO

/* OUTER JOIN - WHERE NULL */

SELECT t1.*,t2.*

FROM Table1 t1

FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID

WHERE t1.ID IS NULL OR t2.ID IS NULL

GO

/* CROSS JOIN */

SELECT t1.*,t2.*

FROM Table1 t1

CROSS JOIN Table2 t2

GO

DROP TABLE table1

DROP TABLE table2

GO

Aggregate functions perform a calculation on a set of values and return a single value.

Following functions are aggregate functions.

AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP

What is the difference between UNION and UNION ALL?

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Difference between having and where clause

select employee, sum(bonus) from emp_bonus group by employee where sum(bonus) > 1000;
 

The SQL above will not work, because the where clause doesn’t work with
aggregates – like sum, avg, max, etc.. Instead, what we will need to
use is the having clause. The having clause was added to sql just so we
could compare aggregates to other values – just how the ‘where’ clause
can be used with non-aggregates. Now, the correct sql will look like
this: 

GOOD SQL:select employee, sum(bonus) from emp_bonus group by employee having sum(bonus) > 1000;

SQL Index

Index in sql is created on existing tables to retrieve the rows quickly.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Syntax to create Index:

CREATE INDEX index_name 
ON table_name (column_name1,column_name2...); 

Syntax to create SQL unique Index:

CREATE UNIQUE INDEX index_name 
ON table_name (column_name1,column_name2...); 

index_name is the name of the INDEX.
table_name is the name of the table to which the indexed column belongs.
column_name1, column_name2.. is the list of columns which make up the INDEX.


In Oracle there are two types of SQL index namely, implicit and explicit.

Implicit Indexes:

They are created when a column is explicity defined with PRIMARY KEY, UNIQUE KEY Constraint.

Explicit Indexes:

They are created using the "create index.. " syntax.


NOTE:
1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently.
2) Is is not required to create indexes on table which have less data. 
3) In oracle database you can define up to sixteen (16) columns in an INDEX. 

A view is, in essence, a virtual table. It does not physically
exist. Rather, it is created by a query joining one or more tables.


Creating a VIEW

The syntax for creating a VIEW is:


CREATE VIEW view_name AS

            SELECT columns

            FROM table

            WHERE predicates;


For example:

CREATE VIEW sup_orders AS

            SELECT suppliers.supplier_id, orders.quantity, orders.price

            FROM suppliers, orders

            WHERE suppliers.supplier_id = orders.supplier_id

            and suppliers.supplier_name = 'IBM';


This would create a virtual table based on the result set of the select statement. You can now query the view as follows:


SELECT *

            FROM sup_orders;


What is Trigger?



A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored
 in and managed by the DBMS. Triggers are used to maintain the 
referential integrity of data by changing the data in a systematic 
fashion. A trigger cannot be called or executed; DBMS automatically 
fires the trigger as a result of a data modification to the associated 
table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored
 procedures are explicitly executed by invoking a CALL to the procedure 
while triggers are implicitly executed. In addition, triggers can also 
execute stored procedures.


Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE 
logic within itself, so when the trigger is fired because of data 
modification it can also cause another data modification, thereby firing
 another trigger. A trigger that contains data modification logic within
 itself is called a nested trigger. 

1. What is the difference between module and class module?

Standard Module: Properties methods and variable declare in this module can be accessed throughout the application.
Class Module: Properties Methods and variables declare in the class module can be accessed only after creating the object of the class. After creating the object you can access these properties and method from any applications both class and module are a collection of methods and properties. The major difference is that instances can be
created from a class in the form of objects, but not from a module. The scope of the members of a class is only for the lifetime of the object, but the scope of the members of a module exist for the life of the program 

 2. What is the difference between procedure and functions?

A procedure is a set of code that does the work but does not return a value whereas a function accepts parameters and does the calculation and does return a valueback.



 

1. Differences between UDF and Stored procedures?

1. Procedure can return zero or n values whereas function can return one value which is mandatory.

2. Procedures can have input, output parameters for it whereas functions can have only input parameters.

3. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

4. Functions can be called from procedure whereas procedures cannot be called from function.

5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6. We can go for transaction management in procedure where as we can't go in function.

7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

 
2. Difference between temporary table and derived table?

a. Using temp table you can able insert the value only at runtime.
In derived table you can insert the data anytime and view also anytime.

b. Explicit Indexing and Constraints are allowed in temp tables.
Explicit Indexing and Constraints are not Applicable in derived tables.

 

3. Can we have more then one primary in one table?

No, in the entire table, there will be only one primary key.
A primary key is nothing but a collection of one or more
columns used to identify a row in a table.
 


 

 

What is Stored procedure?
A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.
They allow modular programming.
They allow faster execution.
They can reduce network traffic.
They can be used as a security mechanism.

 What are the different types of Storage Procedure?
1. Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.

2. System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)

3. Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

4. User stored procedure. 

How do I mark the stored procedure to automatic execution?
You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start. Only objects in the master database owned by dbo can have the startup setting changed and this option is restricted to objects that have no parameters.
USE master
EXEC sp_procoption 'indRebuild', 'startup', 'true')

 How will know whether the SQL statements are executed?
When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred.

 Why one should not prefix user stored procedures with sp_?
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.
36. What can cause a Stored procedure execution plan to become invalidated and/or fall out of cache?

Server restart
Plan is aged out due to low use
DBCC FREEPROCCACHE (sometime desired to force it)                       

                When we need to recompile stored procedure?
If a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).
 SQL Server provides three ways to recompile a stored procedure
The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.
Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.
You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.
39. How will you handle Errors in Sql Stored Procedure?
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END

  How will you raise an error in sql? 

 RAISERROR - Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message. 

 

 What’s the difference between a primary key and a unique key? 

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only. 

 Write a SQL Query to find first Week Day of month
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay How to find 6th highest salary from Employee table? 

 SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary 
 

What are the differences between UNION and JOINS? 

A join selects columns from 2 or more tables.
A union selects rows.

 
How to choose between a Clustered Index and a Non-Clustered Index?
Consider using a clustered index for:

Columns that contain a large number of distinct values.
Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
Columns that are accessed sequentially.


Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
data rows are not sorted and stored in order based on their non-clustered keys.
The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.
Per table only 249 non clustered indexes. 

 What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.


System data bases in sqlserver ?





Here’s
a Closer Look at Each System Database …





The
master Database





The master database stores all the
system-level information for SQL Server. The data stored by the master database
includes information for: configuration settings, logon accounts, linked
servers and endpoints, user database file locations, and properties. 





Due to the nature of the data
stored, SQL Server cannot operate without the master database. So it is a very
good idea to backup this database after changing the SQL Server configuration,
modifying, adding, or removing any databases. 





The
model Database



SQL Server uses the model database
for creating new databases. When the “create database” statement is used, SQL
Server copies the contents of the model database to the newly created database.
If there are any common database objects that would prove useful in all
databases created by SQL Server, it is possible to add those objects to the
model database. Then when a database is created by the SQL Server instance, the
user defined objects will be copied to it along with the default objects. Since
SQL Server recreates the tempdb database every time it is started, the model database
is required in order for SQL Server to start. 


The
msdb Database

The msdb database is used by SQL
Server to store information on operations performed by SQL Server. This
includes information for: the SQL Server Agent, Database Mail, the Service Broker,
log shipping, backup, and maintenance plan job parameters. 

msdb Database
The msdb database is used by SQL Server Agent for alerts and jobs, and also
for Database Mail (among other things). Backup information is stored in the
msdb database. 






The
resource Database

The resource system database was
introduced with SQL Server 2005. This database is used for storing all the
system views and stored procedures. Logically, each SQL Server database will
contain all these system objects, however, they are physically stored within
the resource database. The resource database is read-only and does not include
any user data. 

In previous versions of SQL Server,
the system objects were stored in the master database. The motivation behind
moving the objects to a separate database is to make updating the SQL Server
more efficient. Improvements and fix-ups to the SQL Server system generally
manifest mostly on the system objects. A separate database to store the system
objects reduces the number of files that need to be replaced with an update. 

The
tempdb Database

As the name implies, SQL Server uses
the tempdb database for storing temporary data and data objects. The tempdb
database is used when an operation requires a temporary table, stored
procedure, or other database object to be performed. Intermediary data for
large sort operations is also stored in the tempdb database as well as
temporary data for internal SQL Server operations. 

Every time SQL Server is restarted,
the tempdb system database is recreated thus clearing any temporary data stored
during the last SQL Server session. In cases where a high volume of users and
operations are performed with SQL Server the tempdb database can grow to use a
significantly large amount of disk space. It is important to plan accordingly
in these scenarios since running out of disk space where the tempdb database is
stored will have catastrophic effects on the operation of SQL Server. 

System
Database Maintenance is Essential …

SQL Server’s system databases are
crucial to the operation of SQL Server. If any of the system databases were to
become corrupted, chances are SQL Server will no longer be able to function.
For this reason it is just as important (if not more important) that you backup
the system databases as regularly as the user databases. This applies to all
the system databases except for the tempdb and resource databases. 

Users are restricted from performing
many operations on the system databases such as: changing the database owner,
dropping the guest user, or mirroring. The databases cannot be dropped or set
to offline, nor do the database files support renaming.