Change schema of all tables in database

To change schema of any table/stored procedure you can use this command: ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName But when you want to change schema of all table/sp of a database then its not a good idea to write such lines for all objects. better idea is to generate such script and then run it.. to generate such script for all tables, stored procedures and views, you can use below script.

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER ' + SysSchemas.Name + '.' + DbObjects.Name + ';'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

now just copy the output of this script and run...

Compiled by: Rajesh Rolen

Read More

SQL Server parameter sniffing

While working on a project which has huge database interaction and i was using stored procedure to get good performance. But even though i was using stored procedure the performance was not so good all the time. I mean it was not consistent, for few queries on same stored procedure it was so good and some times it was going worst.

I have done lots of google for it and finally i got the solution.. The easiest solution which you can apply to any existing stored procedure without doing any major changes in it..

Its mostly helpful in situation where you have a stored procedure and the amount of data returned is varying wildly depending on what parameters are passed in.

Some time you will see that the performance of stored procedure change dramatically depending on how much data is being returned.

Then you could be a victim of SQL Server Parameter sniffing.
[YOU ARE AT RIGHT PLACE TO GET SOLUTION]


Normally we writes procedure like:

CREATE PROCEDURE GetUserData
   @UserName nvarchar(20)
    AS
    BEGIN
        SELECT DisplayName, FirstName, LastName 
        FROM dbo.User
        WHERE UserName = @UserName
    END

Now to get good speed constantly, just do some minor changes in it:

CREATE PROCEDURE GetUserData
        @UserName nvarchar(20)
    AS
    BEGIN
        DECLARE @myUserName nvarchar(20)
         SET @myUserName = @UserName
     
        SELECT DisplayName, FirstName, LastName 
        FROM dbo.User
       WHERE UserName = @myUserName
   END

I have done some minor changes in it.
that is, i have just declared local variables and assigned the values of parameters in it and then used those variables in queries instead of using parameters directly.

Now You must be thinking that how come it affect the performance.
Let me explain:

Here is the Microsoft definition:

“Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.


So For some strange reason, when you pass the parameters (@UserName in this case) to a local variable (@myUserName) SQL Server will no longer use the value of the parameter (@UserName) to influence the query plan and you will get the performance constantly. :)

Compiled By: Rajesh Rolen

Read More

How to Query for single value for multiple column in fulltext search

Search a value in multiple column using fulltext search:

select * from productmaster where FREETEXT((productName,productDetails),'Search this text')

Compiled By: Rajesh Rolen

Read More

Send Email from SQL-Server Database

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

The following is the process for sending emails from database.

- Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.

- Write a script to send an e-mail. The following is the script.

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mathew@xyz.com; saadhya@xyz.com;anirudh@pqr.com’ 
@body = ' A warm wish for your future endeavor',
@subject = 'This mail was sent using Database Mail' ;
GO 

References:
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Compiled By: Rajesh Rolen

Read More

How to get @@Error and @ROWCOUNT at the same time?

if @@Rewcount is checked after error checking statement then it will have '0' as the value of @@Recordcount as it would have been reset. and if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.

select @recCount = @@Rowcount, @Err = @@error

Compiled By: Rajesh Rolen

Read More

What is Table Scan?

Table Scan comes to picture when you search for data in a table and you table does't have any index created on it or your query does't take advantage of any existing index of table.
In normal condition Table Scan is not good but in some circumstances its good to use.

A table scan is the easiest and simplest operation that can be performed against a table. It sequentially processes all of the rows in the table to determine if they satisfy the selection criteria specified in the query. It does this in a way to maximize the I/O throughput for the table.

A table scan operation requests large I/Os to bring as many rows as possible into main memory for processing. It also asynchronously pre-fetches the data to make sure that the table scan operation is never waiting for rows to be paged into memory. Table scan however, has a disadvantage in it has to process all of the rows in order to satisfy the query. The scan operation itself is very efficient if it does not need to perform the I/O synchronously.

Now lets understand what exactly happens during Table Scan

Reads all of the rows from the table and applies the selection criteria to each of the rows within the table. The rows in the table are processed in no guaranteed order, but typically they are processed sequentially.

When Table Scan isn't Bad thing

If you are retrieving every row from a table, and plan on using it, it doesn't really matter that you are doing a table scan because there is really no other way to do it. Another time that it doesn't matter so much is when there is very little data in a table, like a type table or a small lookup table, because SQL Server can intelligently cache data when it needs to, and when there is very little data it can actually degrade performance some when it does have an index because of the overhead of maintaining the index. Striking a good balance of indexing vs. non-indexing is a skill that will come with time and experience.

Ex:

SELECT * FROM Employee
WHERE WorkDept BETWEEN 'A01'AND 'E01'
OPTIMIZE FOR ALL ROWS

References:
http://strangenut.com/blogs/dacrowlah/archive/2008/02/24/what-is-a-table-scan.aspx

Compiled By: Rajesh Rolen

Read More

Can we create clustered index on non primary key column

Yes we can create clustered index on non-primary key column but that column must be unique and the primary key column of that table must have non-clustered index.
By default primary key column contains clustered index so its recommended to create such non-primary key clustered index column first and then should create primary key column so in such case the primary key on that column will be with non-clustered.
But its highly recommended to create primary key column as a clustered indexed column.

Compiled By: Rajesh Rolen

Read More

Magic Tables in SQL Server

Magic Tables

There are two Magic tables available in SQL Server named "Inserted" and "Deleted".
These are mantained by SQL server for Internal processing whenever an update, insert of delete occur on a table.
so These are not physical tables, only Internal tables

When ever insert statement is fired the "Inserted" table is populated with newly inserted Row and
when ever delete statement is fired the "Deleted" table is populated with the deleted row.
in same way when update statement is fired both "Inserted" and "Deleted" table used for records, the Original row before updation get store in "Deleted" table and new row (Updated) get store in "Inserted" table.

The term “magic table” is nowhere in the official product documentation. “Magic tables” is apparently .NETspeak for “inserted and deleted tables”

- SQL Server creates and manages them.
- They live in memory.
- These tables are temporary, and only accessible from the statement that created them.

This Magic table are used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only.

But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.

Using with Triggers:
If you have implemented any trigger for any Tables then,
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table.
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only.

These magic table are used inside the Triggers for tracking the data transaction.

Using Non-Triggers:
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

For PHP Bros Please refer : http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Compiled By Rajesh Rolen

Read More

Get Ranking using ROW_Number() without specific ordering in SQL Server

As we all knows that, to get ranking with query we use ROW_Number() in sql server but this issue with Row_Number() is that it only works with "order by" and sometimes we face a situation where we wants the ranking without doing any change in its ordering. To do so, you can do a trick to provide order by to Row_Number as well as it will not affect ordering of your result.

With ordering, this will provide result with row_number but in asc ordering by field1:

 select field1, field2,ROW_NUMBER () OVER (ORDER BY field1) AS RowNum from TableName

Without ordering, this will provide result in order as of query with row number:

 select field1, field2,ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS RowNum from TableName

so we have used "(select 1)" instead of specifying any field name for ordering so it will not affect order of query result

Compiled By: Rajesh Rolen

Read More

Multi record Insert into column with unique key constraint and ignore duplicate

Lets say i have got a table named tblcity and it contains two columns cityid and cityname.
this table contains lets say 1000 records.
now wants to insert some cities from a another table into this table and i wants that if city already exists in tblcity then it should not be reinserted.

to do so, mysql provides very good facility to ignore duplicate like:

INSERT IGNORE INTO Table2(Id, Name) SELECT Id, Name FROM Table1

Source: My Dear Friend Jitendra Dhoot

unfortunately sql server not provide this facility to use "ignore" keyword to ignore duplicate values while performing bulk insert.

To do so in sql server you will have do:
When you are creating a unique index on column, at that time you can set it to "ignore duplicates", in which case SQL Server will ignore any attempts to add a duplicate.

Otherwise we have 3 other option:

Using NOT EXISTS:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS(SELECT id
                    FROM TABLE_2 t2
                   WHERE t2.id = t1.id)
Using NOT IN:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE t1.id NOT IN (SELECT id
                       FROM TABLE_2)
Using LEFT JOIN/IS NULL:

INSERT INTO TABLE_2
  (id, name)
   SELECT t1.id,
          t1.name
     FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.id = t1.id
    WHERE t2.id IS NULL

Compiled By Rajesh Rolen

Read More

Get all child nodes of parent in self join table Tree and vice versa

Lets say we have a table with fileds "category_id, category_name, parentid" now we wants to retrieve all child nodes of specific node then below query will help you.

;WITH Descendants AS (
 SELECT  p.category_name
                , p.category_id
                , 0 AS HLevel
 FROM dbo.categorymaster p  
 WHERE category_id = '7'
 UNION ALL
 SELECT p.category_name
                , p.category_id
                , H.HLevel+1
 FROM dbo.categorymaster p  
 INNER JOIN Descendants H
 ON H.category_id=p.parentid  
)  
SELECT category_id, REPLICATE('     ', Hlevel) + category_name AS category_name
FROM Descendants d

To get all ancestors of a node:

;WITH Ancestors
AS 
( 
 SELECT  p.category_name
  , p.category_id
  , parentid
  , 1 AS HLevel
 FROM dbo.categorymaster p  
 WHERE category_id = 9
 UNION ALL
 SELECT p.category_name
  , p.category_id
  , p.parentid
  , H.HLevel+1
 FROM dbo.categorymaster p  
 INNER JOIN Ancestors H
 ON H.parentid=p.category_id  
)  
SELECT category_name, category_id, HLevel
FROM Ancestors c 

References:

http://jsimonbi.wordpress.com/2011/01/14/sql-hierarchies-parent-child/
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Read More

Deny User to use "Select *" on SQL Table

As we all knows that if we use "Select *" in any query it reduce performance and its not a good practice. this is not best practice to use "select *". user/developer must pass field's name instead of using "*".
as normally every developer takes care about it but even though there is possibility to use it in queries.
so if you wants that no query with "select *" should work then you can do it using just single statement.

add a column to table on which you don't want "select *" to be run.
like:

alter table tablename add column 
 DummyColumn CHAR(1) NULL

now deny rights to run "select" our newly added column named "dummycolumn" for user.

DENY SELECT ON OBJECT:: dbo.yourTableName(DummyColumn) TO your_user;

now if you will try to run
Select * from tableName
you will encounter this error:
The SELECT permission was denied on the column 'DummyColumn' of the object 'YourTableName", database 'YourDatabaseName', schema 'dbo'.

The only way to use this table is by passing column names with select like
select firstcolumnName, secondecolumnName from yourTableName

There are a few things that this user cannot do such as: COUNT(*), COUNT(1) or even SELECT 1 from this table. But there is a way around it. Replacing the * or 1 with the primary key on that table gives the desired results.

I agree it seems too much work to make sure that the users don't use SELECT *, but if you really want to enforce it, here's one way to go about it. But if you know better then please suggest me..

Solution By: Rajesh Rolen.

Read More

Types of Triggers

There are two type of triggers:- Row-level and statement-level triggers. A row-level trigger fires once for each row that is affected by a triggering event. For example, if deletion is defined as a triggering event on a table and a single DELETE command is issued that deletes five rows from the table, then the trigger will fire five times, once for each row.

In contrast, a statement-level trigger fires once per triggering statement regardless of the number of rows affected by the triggering event. In the prior example of a single DELETE command deleting five rows, a statement-level trigger would fire only once.

The sequence of actions can be defined regarding whether the trigger code block is executed before or after the triggering statement, itself, in the case of statement-level triggers; or before or after each row is affected by the triggering statement in the case of row-level triggers.

In a before row-level trigger, the trigger code block is executed before the triggering action is carried out on each affected row. In a before statement-level trigger, the trigger code block is executed before the action of the triggering statement is carried out.

In an after row-level trigger, the trigger code block is executed after the triggering action is carried out on each affected row. In an after statement-level trigger, the trigger code block is executed after the action of the triggering statement is carried out.

Solution By: Rajesh Rolen

Read More

Rebuild index of complete database

for maintaining performance of queries we need to rebuild indexes so below is the way we can use to rebuild all indexes of complete database so we need not to rebuild indexes of all tables one by one.

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Solution By: Rajesh Rolen

Read More

Remove Scripts from database/ Remove Sql Injections

USE [DBFORUMSNEW]
GO
/****** Object:  StoredProcedure [dbo].[sp_sqlinjection]    Script Date: 11/26/2010 19:46:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_sqlinjection]  
@DBName varchar(100)  
as  
Begin  
declare @DB_Name varchar(40), @Table_Name varchar(40),@Column_Name varchar(40)  
declare @sql1 varchar(1000), @sql2  varchar(1000)
Set @DB_Name  = @DBName  
begin  
exec ('use ' + @Db_Name)  
 DECLARE Table_Cursor CURSOR FOR SELECT name from sysobjects where type= 'U'  
 OPEN Table_Cursor  
 FETCH NEXT FROM Table_Cursor into @Table_Name  
 WHILE @@FETCH_STATUS = 0  
 begin  
  declare Column_Cursor CURSOR for select name from syscolumns where id = object_id(@Table_Name) and xtype in (239,175,231,167) and length  > 20  
  open Column_Cursor  
  Fetch next from Column_Cursor into @Column_Name  
  WHILE @@FETCH_STATUS = 0  
  begin  
  set @sql1='if exists (SELECT 1 FROM ['+@Table_Name+'] where ['+@Column_Name+'] like ''%< script%'')  
                begin
                update ['+@Table_Name+'] set ['+@Column_Name+'] = replace(['+@Column_Name+'],  
substring(['+@Column_Name+'],charindex(''< script'',['+@Column_Name+']),  
case when charindex(''< /script'',['+@Column_Name+']) >charindex(''< script'',['+@Column_Name+']) then  
charindex(''< /script'',['+@Column_Name+'])-charindex(''< script'',['+@Column_Name+'])+9  
else   
len (['+@Column_Name+'])  
end ),  
'''') where ['+@Column_Name+'] like ''%< script%''
 end '     
   exec (@sql1)  
     set @sql2='if exists (SELECT 1 FROM ['+@Table_Name+'] where ['+@Column_Name+'] like ''%< title%'')  
                begin
                update ['+@Table_Name+'] set ['+@Column_Name+'] = replace(['+@Column_Name+'],  
substring(['+@Column_Name+'],charindex(''< title'',['+@Column_Name+']),  
case when charindex(''< /title'',['+@Column_Name+']) >charindex(''< title'',['+@Column_Name+']) then  
charindex(''< /title'',['+@Column_Name+'])-charindex(''< title'',['+@Column_Name+'])+9  
else   
len (['+@Column_Name+'])  
end ),  
'''') where ['+@Column_Name+'] like ''%< title%''
 end '     
   exec (@sql2)  
   FETCH NEXT FROM Column_Cursor into @Column_Name  
  end  
         close Column_Cursor  
  DEALLOCATE Column_Cursor  
         FETCH NEXT FROM Table_Cursor into @Table_Name  
 end  
 CLOSE Table_Cursor  
 DEALLOCATE Table_Cursor  
 --FETCH NEXT FROM DB_Cursor into @DB_Name  
end  
end

Solution By: Rajesh Rolen

Read More

difference between exec and sp_executeSql

both are used for executing dynamic sql in sql server.
both are not preferred because, If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.So, we have to think about the performance while using dynamic sql.

Main Difference:

Exec is non parametrized
sp_executeSql is parametrized

the main difference between above two is difference in performance.
the for Exec the execution plan is created every time it gets executed where as with sp_executeSql the same execution plan (will try) will be used only its value will be changed.

but if both's queries are getting changed on every call than there is no difference between them. but even though the plus point is with sp_executeSql that if their comes same query to it than it use same execution plan with different parameter and perform operation faster with compare to Exec.

Example:

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID -- this will be faster because its only value will be changed and will use same execution plan.

Solution By:

Rajesh Rolen

Read More

How to refresh view when table structure got changed

Lets we have got a table named tblstudent which contains following fields

studentRNo, studentName, StudentAddress

now we have create a view on it like:

create view viewName
as
select * from tblstudent

now when we will perform select operation on view then it will show us columns:

select * from viewName

output:
studentRNo, studentName, StudentAddress

now we have added a new column to tblstudent named 'studentFee'

alter table tblstudent add StudentFee int

now when we will run select operation on view again like:

select * from viewName

it will show use only those three columns which was exists while creating view:

select * from viewName

output:
studentRNo, studentName, StudentAddress

so as we are using '*' in query so it should show the newly added column in result but it will not show it. so to get desired result you will have to use a system storedprocedure 'sp_refreshView'

sp_refreshView 'ViewName'

and its done.

select * from viewName

output:
studentRNo, studentName, StudentAddress, studentFee

Solution by

Rajesh Rolen

Read More

Disable Trigger

To disable the trigger use this syntex:

DISABLE TRIGGER triggerName ON TableName;

To disable all triggers of available server:

DISABLE Trigger ALL ON ALL SERVER

Solutions By:Rajesh Rolen

Read More

Rebuild index while insert/update is also available

Some times we face such a situation that we have data in GB of size and we want to rebuild our index which is applied on that table. but in previous editions from MS-Sql server2005 this was not possible to provide insert/update facility while performing rebuild index operation.

but from sql server2005 we have got this very good facility to make insert/update available (working) while rebuild index is under progress.

to do so the syntax is :

CREATE CLUSTERED INDEX cl_SalesHistory_SaleID ON SalesHistory(SaleID ASC, SaleDate ASC)
 WITH(DROP_EXISTING = ON, ONLINE = ON)

in above query we have made our table available/allowing for insert/update while we perform rebuild index operation on it.

ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

Solutions By:Rajesh Rolen

Read More

Import data from .csv to sql

to import data from .csv file to sql server you can use below query.

BULK INSERT ZIPCodes 
    FROM 'e:\yourCSVFileName.csv' 
    WITH 
    ( 
  FIRSTROW = 2 ,
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

Read More