Getting information on MS SQL Server objects .

 Tables and Views
                  To get all tables, views, and system tables, the following SQL Server system stored procedure can be executed.

exec sp_tables '%'

To filter by database for tables only, for example master:

exec sp_tables '%', '%', 'master', "'TABLE'"

To filter by database and owner / schema for tables only, for example, master and dbo:

exec sp_tables '%', 'dbo', 'master', "'TABLE'"

To return only views, replace "'TABLE'" with "'VIEW'". To return only system tables, replace "'TABLE'" with "'SYSTEM TABLE'".

Schemas / Owners

Here are two examples for queries to get schema / owner information.

select distinct SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA order by SCHEMA_NAME

select name from dbo.sysusers where islogin = 1 order by name

Procedures

This is a query to get all MS SQL Server procedures.

exec sp_stored_procedures '%'

The query can be filtered to return procedures for specific schemas / owners and databases by appending more information onto the procedure call, such as the following:

exec sp_stored_procedures '%', 'dbo', 'master'

Procedure Parameter 

This is a system stored procedure call to get the columns in a SQL Server procedure.

exec sp_sproc_columns 'get_employee_names', 'dbo', 'sample'

Functions

This is a query to get all MS SQL Server functions.

select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where upper(ROUTINE_TYPE) = 'FUNCTION'

Triggers

This is a query to get all MS SQL Server triggers.

select * from sysobjects where type = 'TR'

The query can be filtered to return triggers for a specific owner by appending a user_name call onto the where clause to the query.

select * from sysobjects where type = 'TR' and user_name(sysobjects.uid) = 'dbo'

Indexes

This is a query to get MS SQL Server indexes for a particular table. In this example, the table used is employee.

exec sp_helpindex 'employee'

Database Replication

                     Database replication is the frequent electronic copying data from a database in one computer or serverto a database in another so that all users share the same level of information. The result is a distributed databasein which users can access data relevant to their tasks without interfering with the work of others. The implementation of database replication for the purpose of eliminating data ambiguity or inconsistency among users is known as normalization.
Database replication can be done in at least three different ways:

• Snapshot replication: Data on one server is simply copied to another server, or to another database on the same server.
• Merging replication: Data from two or more databases is combined into a single database.
• Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.

A distributed database management system (DDBMS) ensures that changes, additions, and deletions performed on the data at any given location are automatically reflected in the data stored at all the other locations. Therefore, every user always sees data that isconsistent with the data seen by all the other users.

What is Linked Servers or Database Links?

                  Don't be confused by the two terms; both are the same. In SQL Server it is called a Linked Server whereas in Oracle it's DBLinks (Database Links).

Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers. 

It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers. 

The remote servers can be SQL Server, Oracle etc. which means those databases tht support OLE DB can be used for linking servers.

               After setting up the Linked Servers we can easily access the other server tables, procedures etc. 

After establishing a connection we can even do CRUD operations. 

The advantage is about security; its works on Windows as well as SQL Server Authentications.

How to find information about existing Linked Servers?

We can get Linked Server basic information by executing the following stored procedure created in the master database or default system.

exec sp_linkedservers

  OR

using select * from sys.servers will return more information about servers.

 

 This will create a Linked Server; we can view this from Management Studio.

 

SQL database in Transition .Try statement later or Error no 952

                    This error can occur if a database has been taken offline and another statement of query is run against it. This is a reasonable common bug, in SQL 2005 and in the earliest versions of SQL 2008. I must say, I hav'nt seen it in SQL 2008 R2 yet. Hope it stays that way. Now lets start with the cause of this error. On purpose, or through lots of other reasons, this can happen with databases. There are allways reasons for taking a database offline, mostly before you can attach another copy, but when it becomes inaccessible then, you can get in serious troubles... The transition state normally takes not more then 20 seconds for a database. But this error is always the result of using the SSMS itself. Setting a database offline requires absolute exclusive access to the Database and if any connection is this open, this error will occur. But do not worry! Now I'm certainly not a database administrator, but I picked up a few possibilities on the way which I state here below, keeping the SQL instance as long as possible online. But first to the actual error message. Most of the times it will be something like the picture here on the right en the text will be:
  we cansolve this issue by many wys.But easiest solution  which worked for me.
A little more drastic now, create a new query... Something like the lines below,.... Replace the YOURDBNAME with the correct name of your troubling SQL-database.

USE MASTER
GOALTER DATABASE  [YOURDBNAME]SET OFFLINE WITH ROLLBACK IMMEDIATE

and if you want to make it offline

ALTER DATABASE YOURDBNAME SET ONLINE

This statement will absolutely take your database offline.

Can I access Temporary table of one procedure into another prcocedure in sql server?Is it possible?

  Yes.You can use.
Run following Script :

if exists(select name from sys.procedures where name='sp1')
drop proc sp1
go
create procedure sp1
as
begin

select * from #t1

end
go
Go
if exists(select name from sys.procedures where name='sp2')
drop proc sp2
go
go
create procedure sp2
as
begin
select 'SP1 Table' [Value] into #t1
 exec sp1
end
go
 exec sp2

Graphical Query Plan

When SQL Server executes a query it uses a query plan to determine how to access data and complete the query.  SQL Server offers DBAs and developers the ability to see these query plans to assist in query optimization.  Without query plans it would be difficult to figure out how to tune and optimize your queries.

One such version of query plans is Graphical Execution Plans which allows you to see a graphical representation of the processing steps of your query to find optimization opportunities.

The Query Execution Plans describe the steps and the order used to access or modify data in the Microsoft SQL Server database. In this tutorial we will use the terms Execution Plan, Query Plan and Query Execution Plan interchangeably.

Briefly, the Query Plan defines how SQL statements are physically executed by the server. The Query Plan describes the data retrieval and storage methods that are used by the Query Optimizer to execute a specific query. For example, it includes whether the whole table should be read or if an index is used to read a small number of records.

The Execution Plan consists of different operations and each operation has one output which is called the result set. The operations can have one or more inputs such as join operations that have two inputs. Each result set (output) will be the input for the next operation until the SQL statement is finished executing. Therefore the data flow can be drawn as a connection between operators from right to left.

There are many potential ways to execute a query thus SQL Server has to choose the most beneficial one. In the case of very complex queries where there can be many variations, so SQL just picks a plan that is good enough.

The executed Query Plans are also stored in the Procedure Cache, so they can be retrieved and reused if a similar query is executed.

SQL Server can create plans in two ways:

    Actual Execution Plan - created after execution of the query and contains the steps that were performed
    Estimated Execution Plan - created without execution of the query and contains an approximate execution plan

SQL Tuning or SQL Optimization

 SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;



2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';



3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

Instead of:

SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';



4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items



5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;



6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;



7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb