Dec 31, 2013

Sql query to get script or text of any stored procedures without modifying in sql server


Sql query to get script or text of any stored procedures in sql server

We can get the script or text of stored procedure without modifying the procedure by using the system stored procedure sp_helptext. Synatx:

EXECUTE sp_helptext <ProcedureName>

For example:

EXECUTE sp_helptext 'usp_getdata'

Other sql script:

SELECT m.definition 
FROM sys.sql_modules m INNER JOIN Sys.procedures p
ON m.object_id = p.object_id AND p.name = 'AppProc'

Sql query for DELETE all the records or rows of all the tables in database in sql server


Sql query for DELETE all the records or rows of all the tables in database in sql server

We can delete all the records or rows of all tables of sql of given database using system stored procedure sp_MSforeachtable. For example:

EXECUTE sp_MSforeachtable N'SELECT * FROM ?'

If you have any quires you can ask here.

Sql query for SELECT all the records or rows of all tables in database in sql server


Sql query for SELECT all the records or rows of all tables in database in sql server

We can select all the records or rows of all tables of sql of given database using system stored procedure sp_MSforeachtable. For example:

EXECUTE sp_MSforeachtable N'SELECT * FROM ?'

If you have any quires you can ask here.

Dec 30, 2013

Cannot create nonunique clustered index on view '' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.


Cannot create nonunique clustered index on view '' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.

We cannot create the non-unique clustered  index on view. That is we must have to create clustered index using UNIQUE keyword. If we will create index like

CREATE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(RollNo)

We will get error message:

Cannot create nonunique clustered index on view 'dbo.view_StuInfo' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.

Solution:

CREATE UNIQUE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(RollNo)

Cannot create index on view ''. It does not have a unique clustered index.


Cannot create index on view ''. It does not have a unique clustered index.

We cannot create the non-clustered index on view untill we have not first created the unique clustered index on view. For example:
First create unique clustered index on view:

CREATE UNIQUE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(RollNo)

Now we can create any numbers of non-clustered index on view.

CREATE NONCLUSTERED INDEX NCI_ViewStu
ON dbo.view_StuInfo(Age)

Cannot create index on view '' because the view is not schema bound.


Cannot create index on view '' because the view is not schema bound.

When we will create an index on view

CREATE UNIQUE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(Age)

We may get error message like:
Cannot create index on view 'view_StuInfo' because the view is not schema bound.

Since we can create an index on only those view which are schema bind with base table. Scheama bind means we cannot change the schema of the base objects unless and unill we don't remove the schema binding view  or alter or drop the view.

Solution:

DROP VIEW view_StuInfo
CREATE VIEW view_StuInfo WITH SCHEMABINDING
AS
SELECT Age,Name FROM dbo.Student

Note: It is necessay to specify the schema name (dbo) in the object.

Dec 23, 2013

Sql script to get the version of sql server


Sql query to get the version operating system where it has installed:

SELECT @@VERSION AS [Version]

Sample output:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 6.1 <X86> (Build 7600: )

Sql script to get the IO time in sql server


Sql script to get the input output busy time in sql server since last time sql server had started.

SELECT @@IO_BUSY AS [IO Time]

Sample Output:

IO Time
120

To get in second:

SELECT @@IO_BUSY * CAST(@@TIMETICKS AS FLOAT) / 1000000 AS [IO Time(Second)]

To get the IO time during the execution of some sql queries:

DECLARE @IOTime AS INT
SET  @IOTime = @@IO_BUSY

/*
    Sql statements
*/

SELECT @@IO_BUSY -  @IOTime AS [IO Time]

Dec 15, 2013

FOR XML PATH error in column '' - '//' and leading and trailing '/' are not allowed in simple path expressions sql server


I am creating a tblOrder table in sql server and inserting few records into it:

CREATE TABLE tblOrder(
    OrderID INT IDENTITY PRIMARY KEY,
    Location VARCHAR(50),
    OrderDate DATETIME DEFAULT(GETDATE())
)

INSERT INTO tblOrder(Location) VALUES('China'),('USA'),('India'),('UK'),('India')

If we will execute following sql queries :

1.

SELECT
    OrderID "@Id",
    Location "/Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

2.


SELECT
    OrderID "@Id",
    Location "//Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

3.

SELECT
    OrderID "@Id",
    Location "Country/Location/",
    OrderDate
FROM tblOrder FOR XML PATH
We will get error message:

FOR XML PATH error in column '' - '//' and leading and trailing '/' are not allowed in simple path expressions.

Cause:  It is due in incorrect XML path in column Location.

Solution: Correct XML path can be:

SELECT
    OrderID "@Id",
    Location "Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

Output:


<row Id="AA==">
  <Country>
    <Location>China</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="AQ==">
  <Country>
    <Location>USA</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="Cg==">
  <Country>
    <Location>India</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="Cw==">
  <Country>
    <Location>UK</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="ZA==">
  <Country>
    <Location>India</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>

Dec 14, 2013

Unable to open the physical file "". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)


When we will try to attach AdventureWorks database in sql server we may get error message like:

Unable to open the physical file "". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

Cause of this error is path of physical and log file. To solve this problem correct the physical path of data file and remove the log file as show in the following figure:


I hope this will solve this issue.  

Nov 21, 2013

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.


When we will  open the XML doc using stored procedure sp_xml_preparedocument in sql server while we are not removing it by executing the procedure:

EXECUTE SP_XML_REMOVEDOCUMENT @hdoc

We may get error message:

XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.

Solution:

Step 1:

Get the session_Id by executing the following sql query which is holding the active handler opened by sp_xml_preparedocument

SELECT * FROM SYS.DM_EXEC_XML_HANDLES(0)

Step 2: Now kill the all the session_Id one by one which has returned by above query:

KILL Session_Id

For example:

KILL 175
KILL 76
....

It will solve this problem!!

Could not find stored procedure 'sysmail_start_sp'.

When we will execute the stored procedure in sql server to  start the database mail :

EXECUTE sysmail_start_sp

We may get error message like:
Could not find stored procedure 'sysmail_start_sp'.

There may be many cause of this. One common mistake is we are not executing this procedure in msdb database.

Correct way is:
EXECUTE msdb.dbo.sysmail_start_sp

Or

USE msdb
EXECUTE sysmail_start_sp

Nov 20, 2013

SQL server interview questions and answers with explanation



Frequently asked Sql server interview questions and answers for fresher with explanation
1
Why we cannot use column alias in where clause but we can use it in order by clause of select statement in sql server?
For example, it is incorrect to write:
SELECT Roll_No AS Id From Student WHERE Id > 1

While it correct:
SELECT Roll_No AS Id From Student ORDER BY Id
Answer
Explanation:

In sql server order of execution of different clauses of a select statement is following order:
Clause of select statement
Execution order
FROM
1
ON
2
JOIN
3
WHERE
4
GROUP BY
5
WITH CUBE or WITH ROLLUP
6
HAVING
7
SELECT
8
DISTINCT
9
ORDER BY
10
TOP
11


It is clear the WHERE clause executes before the SELECT clause so WHERE clause has no knowledge about column alias of SELECT clause while ORDER BY clause executes after the SELECT clause so it know about column alias of SELECT clause.
Hide
2
What are the differences between stored procedure and function in sql server?
Answer
Explanation:

1.
Function:
Functions can be part of SELECT statement or DML queries. For example:
Suppose there is a scalar function IsInteger. It is correct to write:
SELECT IsInteger(Roll_No) AS Id From Student
Stored procedure:
Stored procedures cannot be part of SELECT statement or any other DML queries.
2.
Function:
Function can retuen any type of data which sql server supports. For example scalar function can return INTEGER, MONEY, NVARCHAR, TEXT etc. and table valued function can return table variable. For example:
CREATE FUNCTION GetString()
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN 'Exact Help'
END

--To execute function
SELECT dbo.GetString()

Stored procedure:
Return type of a stored procedure can return only integer type data. For example:
CREATE PROCEDURE LoadStudent
AS
BEGIN
    IF @@ERROR = 0
         RETURN 1
    ELSE
         RETURN 0
END

--To execute stored procedure
DECLARE @ReturnValue INTEGER
EXECUTE @ReturnValue = LoadStudent
SELECT @ReturnValue

3.
Function:
A function cannot return any other result set except its return type. For example, it is incorrect to create function like:
CREATE FUNCTION GetString()
RETURNS INTEGER
AS
BEGIN

    SELECT 'Exact Help'
   
    RETURN 1
END
Stored procedure:
A stored procedure can return as many result set as we want apart from its return type. For example:
CREATE PROCEDURE GetData
AS
BEGIN
   
    SELECT 'Exact help' --Result set 1
    SELECT * FROM Student  --Result set 2
   
    RETURN 1
END
4.
Function:
A function cannot have any insert, update, delete, merge statements, DDL queries, try catch block etc. It can have select statement but it cannot return result set. For example:
CREATE FUNCTION GetInfo()
RETURNS INTEGER
AS
BEGIN

    DECLARE @Roll AS INTEGER
    SELECT TOP(1) @Roll = Roll_No  FROM Student

    --It is incorrect to write
    /*
   
         SELECT TOP(1) Roll_No  FROM Student
         INSERT INTO Student DEFAUL VALUE
         DELETE FROM Student
         CREATE TABLE Emp(
             Id AS INT,
             Name AS VARCHAR(100)
         )
   
    */
   
    RETURN @Roll
END
Stored procedure:
A stored procedure can have all DDL and DML statements.
Hide
3
To delete the records of a table which one is better?
a. Using DELETE statement
b. Using TRUNCATE statement
Answer
Explanation:

It will depend upon the requirements:

1. TRUNCATE statement has not any WHERE clause. So if we have to delete a part of records from table we will have to use DELETE statement.

2. TURNCATE statement is faster than DELETE statement since it doesn’t keep the logs for each record. So we have not to keep the logs then we should use TRUNCATE statement otherwise we have to use DELETE statement.

3. TURNCATE statement reset the identity value of table so if we want to reset then we should use TURNCATE otherwise we should use DELETE statement.
Hide
4
What is a global temporary table in sql server?
Answer
Explanation:

In sql there are two types of temporary tables:
1. Local temporary table
2. Global temporary table

Local temporary table name has prefix # while global table has ##. For example:

Creating local temporary table
CREATE TABLE #ExactHelp(
    ID INT,
    Author VARCHAR(100)
)

Creating local temporary table

CREATE TABLE ##ExactHelp(
    ID INT,
    Author VARCHAR(100)
)

Scope of local temporary table is session and global temporary table is login.
Global temporary table is mostly used in dynamic sql for example:

EXECUTE('SELECT * INTO #TempTable FROM Customer')
EXECUTE('SELECT * INTO ##TempTable FROM Customer')

Now if we execute following query:
SELECT * FROM #TempTable

We will get error message: Invalid object name '#TempTable'.

If we execute following query it will work fine.
SELECT * FROM ##TempTable
Hide
5
Why a table can have only one clustered index?
Answer
Explanation:

Clustered index in sql server is stored as B tree. Physical organization of data of table with clustered index is same as clustered index. Any table can have only one physical organization so it can have only one clustered index.
Hide
6
What are the differences between table variable and temporary table in sql server?
Answer
Explanation:

1. Scope of temporary variable is session. For example if we will execute following query in same tab of sql server it will execute fine.

CREATE TABLE #temp(
    ID INT,
    Name VARCHAR(50)
)

Go
INSERT INTO #temp VALUES(1,'Davis')
SELECT * FROM #temp

But if we will execute following query in other tab

SELECT * FROM #temp

We will get error message like: Invalid object name '#temp'.

Note: When we open new query page or tab in sql server management studio it create a new session.

Scope of table variable is batch. For example we will execute following sql queries

DECLARE @table TABLE(
    ID INT,
    Name VARCHAR(50)
)

Go
INSERT INTO @table VALUES(1,'Davis')
SELECT * FROM @table

We get error like: Must declare the table variable "@table"

Note: Go keyword in sql server always creates a new batch.

2. Temporary table is same as normal table with different scope. We can create non – clustered index on temporary table. For example:

CREATE TABLE #temp(
    ID INT,
    Name VARCHAR(50)
)
CREATE NONCLUSTERED INDEX temp_table_index
    ON #temp(ID , Name)

We cannot create a non-clustered index on table variable. For example:

DECLARE @table TABLE(
    ID INT,
    Name VARCHAR(50)
)
CREATE NONCLUSTERED INDEX temp_table_index
    ON @table(ID , Name)

We will get some error message.

Note: If we have large amount of data and performing quires on it then temporary table may be better option than table variable since we can increase the performance of a query by creating indexes on it.  

3. There is not any table lock concept in the table variables. For example it is correct to write:

SELECT * FROM #temp WITH(NOLOCK)

While it is incorrect to write:

SELECT * FROM @table WITH(NOLOCK)

3. Unlike to the temporary table, table variables also not support transaction and logging. For example:

CREATE TABLE #temp(
 ID INT,
 Name VARCHAR(50)
)

BEGIN TRANSACTION
    INSERT INTO #temp VALUES(1,'Davis')
    ROLLBACK
    SELECT * FROM #temp

Select query will not return any rows since it has been rollback while if will perform same operation on table variable for example:

DECLARE @table AS TABLE(
 ID INT,
 Name VARCHAR(50)
)

BEGIN TRANSACTION
    INSERT INTO @table VALUES(1,'Davis')
    ROLLBACK
    SELECT * FROM @table

In this case select query will return one row like
ID
Name
1
Davis

Since table variables doesn’t support transaction and rows has not been roll backed.

4. Tables variables in the stored procedures are pre- compiled while temporary tables are not.

5. We can pass the table variables as a parameter of stored procedures or functions. For example:

 CREATE TYPE InfoType AS TABLE(
    ID INT,
    Name VARCHAR(50)
)

GO

CREATE PROCEDURE GetInfo(
    @table AS InfoType READONLY
)
AS
BEGIN
    SELECT * FROM @table
END

Now we will execute the above stored procedure by passing table variable.

DEClARE @info AS InfoType
INSERT INTO @info VALUES(1,'Davis')

EXECUTE GetInfo @info

Output:
ID
Name
1
Davis

While a temporary table cannot be passed. For example:

CREATE TABLE #temp(
    ID INT,
    Name VARCHAR(50)
)

INSERT INTO #temp VALUES(1,'Davis')
EXECUTE GetInfo #temp

We will get the error message like: nvarchar is incompatible with InfoType

6. A table value function can return table variable but it cannot return temporary table.

7. With help of SELECT INTO we can directly create the temporary table while it is not possible for temporary variables. For example:

SELECT * INTO #temp
FROM Student

It is not possible in case of table variables.

SELECT * INTO @table
FROM Student

If we will execute above query we will get error message like: Incorrect syntax near '@table'
Hide
7
Write a sql query to get all records of table in XML format.
Answer
Explanation:

To get the result set of SELECT statement in the XML format we have to use FOR clause. It has forms
1. AUTO
2. RAW
3. EXPLICIT

For example suppose we have KeyValue table like this:
Id
Key
Value
1
Name
Scott
2
Age
42
3
DOJ
12/05/2011
4
IsActive
1

Sql query to get the data of KeyValue table in XML format:

SELECT  * FROM KeyValue FOR XML AUTO
Output:

<KeyValue Id="1" Key="Name" Value="Scott" />
<KeyValue Id="2" Key="Age" Value="42" />
<KeyValue Id="3" Key="DOJ" Value="12/05/2011" />
<KeyValue Id="4" Key="IsActive" Value="1" />
Hide
8
What is difference between @@IDENTITY  and SCOPE_IDENTITY()
Answer
Explanation:

SCOPE_IDENTITY() function returns the values of identity column of last insered record in the current scope while @@IDENTITY has not such restriction.
Hide
9
Can a function return result set like table in sql server?
Answer
Explanation:

In sql server function can return only value of any data type. This may be table data type. Such function is called table valued function. For example:

CREATE FUNCTION GetData()
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Student
)

--To execute this function
SELECT * FROM dbo.GetData()
Hide
10
Write a sql query to get all the tables of given database.
Answer
Explanation:

In sql server all the objects are stored in the system defined view sys.Objects. There is type column to identify the different type of object. For example:

U: User defined table
P: Stored procedure
FN: scalar function
S: System defined table 

Sql query to get the all user defined table:

SELECT * FROM sys.objects WHERE type = 'U'
Hide
11
Write a sql query to check data of a column is type bit or not in sql server?
Answer
Explanation:

Sql server doesn’t support a function like IsBit.  We can test a given data or column is type of BIT by using the function SQL_VARIANT_PROPERTY. For example:

DECLARE @Var AS BIT = 1
SELECT SQL_VARIANT_PROPERTY(@Var,'BaseType')

Output: bit

Creating IsBit function in sql server

CREATE FUNCTION IsBit(
    @Data AS SQL_VARIANT
)
RETURNS BIT
AS
BEGIN

    DECLARE @IsBit AS BIT
   
    IF SQL_VARIANT_PROPERTY(@Data,'BaseType') = 'bit'
         SET  @IsBit = 1
    ELSE
         SET  @IsBit = 0
        
    RETURN @IsBit
   
END
Examples:

DECLARE @Var1 BIT = 0
DECLARE @Var2 BIT = 'True'
DECLARE @Var3 INTEGER = 1
DECLARE @Var4 VARCHAR = 'True'

SELECT
    dbo.IsBit(@Var1),
    dbo.IsBit(@Var2),
    dbo.IsBit(@Var3),
    dbo.IsBit(@Var4)

Output:
1   1   0   0

Note: In sql server 0 and 1 are integer constants they are not a bit type constants. In the same way 'True' and 'False' are varchar constants not a bit constant. For example:

SELECT
    dbo.IsBit(1),
    dbo.IsBit(0),
    dbo.IsBit('True'),
    dbo.IsBit('False')

Output:
0   0   0   0
Hide
12
Can you define stored procedure in sql sever?
Answer
Explanation:

Stored procedure is batch of sql queries which is compiled into a single execution plan. It can have any number of parameters and one returns code (or return type) which can be only integer type. For example:

CREATE PROCEDURE AppProc(
    @Id AS INT,
    @Name AS VARCHAR(50) OUTPUT
)
AS
BEGIN
    SELECT @Name FROM Student WHERE Roll_No = @Id
    SELECT 'Success'
   
    RETURN 1
END


--To execute the procedure
DECLARE @RrturnCode AS INT
DECLARE @Name AS VARCHAR(50)

EXECUTE @RrturnCode = AppProc 5, @Name OUTPUT
SELECT @Name
Hide
13
Do you know any generic data type in sql server which stores almost all the other types of data?
Answer
Explanation:

SQL_VARIANT is special data type in sql server support the data of other types with few exceptions.
Hide
14
Write a sql query which delete the all records of Student table and insert the deleted records into the Student_Archive tables at the same time.
Answer
Explanation:

Sql server has introduce an output clause in DELETE statement by which we can return a result set of deleted records or insert into the other table. Sql query for this is:

DELETE Student
OUTPUT DELETED.* INTO Student_Archive  
Hide
15
Can you write a sql query to get the records of a table in the CSV (comma separated value)?
Answer
Explanation:

Suppose we have KeyValue like this:

Id
Key
Value
1
Name
Scott
2
Age
42
3
DOJ
12/05/2011
4
IsActive
1

We want to write a sql query which will return data of column key delimited by comma or any other character like this:

CSV
Name,Age,DOJ,IsActive

Sql server query:

DECLARE @SqlQuery AS VARCHAR(MAX) = ''
SELECT @SqlQuery = @SqlQuery + [KEY] + ',' FROM KeyValue
SELECT LEFT(@SqlQuery,LEN(@SqlQuery)-1)
Hide
15
What are the advantages and disadvantages of using index? 
Answer
Explanation:

Advantage of index

Large numbers of indexes can help the performance of queries that do not modify data (SELECT statements) because SQL Server has more indexes to choose from to determine the best way to access the data as fast as possible.

Disadvantage of index

1. Large numbers of indexes on a table affect the performance of INSERT, UPDATE, and DELETE statements because all indexes must be adjusted appropriately as data in the table changes.

2. More disk spaces.

3. If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order.
Hide
16
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
17
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
18
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
19
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
20
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide