Advantages of SQL Server 2008 over SQL Server 2005

Transparent Data Encryption. The ability to encrypt an entire database.
Backup Encryption. Executed at backup time to prevent tampering.
External Key Management. Storing Keys separate from the data.
Auditing. Monitoring of data access.
Data Compression. Fact Table size reduction and improved performance.
Resource Governor. Restrict users or groups from consuming high levels or resources.
Hot Plug CPU. Add CPUs on the fly.
Performance Studio. Collection of performance monitoring tools.
Installation improvements. Disk images and service pack uninstall options.
Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
LINQ. Development query language for access multiple types of data such as SQL and XML.
Data Synchronizing. Development of frequently disconnected applications.
Large UDT. No size restriction on UDT.
Dates and Times. New data types: Date, Time, Date Time Offset.
File Stream. New data type VarBinary(Max) FileStream for managing binary data.
Table Value Parameters. The ability to pass an entire table to a stored procedure.
Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
SQL Server Integration Service. Improved multiprocessor support and faster lookups.
MERGE. TSQL command combining Insert, Update, and Delete.
SQL Server Analysis Server. Stack improvements, faster block computations.
SQL Server Reporting Server. Improved memory management and better rendering.
Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.
SQL 2000 Support Ends. Mainstream Support for SQL 2000 is coming to an end.

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

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

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

Trigger Ordering in Sql Server

Many of times we face such issue that we have two triggers defined on same table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. So how can we make sure that they fire in the correct order to enforce my business logic.

By default, multiple triggers on a SQL Server table for the same action are not fired in a guaranteed order. However, it's possible to declare the firing order for 2 AFTER triggers (i.e. triggers that fire after the database action has been completed) using system stored procedure sp_settriggerorder. This feature cannot be used with INSTEAD OF triggers and you will receive a database error if you attempt to define an order on these types of triggers.

The system stored procedure sp_settriggerorder was introduced in SQL Server 2000 and has been modified to accept a new parameter in SQL Server 2005 to support the new DDL trigger feature. It is defined as follows:

exec sp_settriggerorder @triggername = , /* SQL Server 2000 and 2005 */
@order = [FIRST|LAST|NONE], /* SQL Server 2000 and 2005 */
@stmttype = [INSERT|UPDATE|DELETE|], /* SQL Server 2000 and 2005 */
@namespace = [DATABASE|SERVER|NULL] /* SQL Server 2005 only */

- Parameter @triggername is self explanatory; it's the trigger being ordered.

- Parameter @order indicates whether the trigger should fire FIRST or LAST. If NONE is specified, then no order is enforced.

- Parameter @stmttype indicates the trigger type i.e. whether it's an INSERT trigger, for instance.

- Parameter @namespace is SQL Server 2005 specific and indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger

lets say we have 2 insert trigger named:trg1 and trg2 on same table and we want the trg1 to be fired first and than trg2 should be fired.
so to perform this we will have to set its order using sp_settriggerorder. otherwise their default ordering will be not sure as per required.

Create trigger:

trg1:

create trigger dbo.trg1 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 1'

trg2:

create trigger dbo.trg2 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 2'


Setting order of trigger:
exec sp_settriggerorder @triggername = 'trg1',
@order = 'first',
@stmttype = 'insert',
@namespace = null

exec sp_settriggerorder @triggername = 'trg2',
@order = 'last',
@stmttype = 'insert',
@namespace = null
go

Solutions By: Rajesh Rolen

Read More

What is Link Server and what are its advantages in SQL Server/ How to write Queries on tables and database which are on seperate sql server instance

Basically it provides us facility of Distributed transactions.

Some times we want to retrieve data from a sever and insert it in table of another server.
or
Some times we want to run sql queries and perform joins on tables of different database whose database are on different computer.

Link server provides us all above facility

Linked Servers allow you to submit a TSQL statement on one SQL Server instance, which retrieves data from a different SQL Server instances. In fact, linked server can be used to join data from multiple SQL Server instances using a single TSQL statement. When you have databases on multiple SQL Server instances, you might find it useful to use linked servers in your application to retrieve data from more than one instance. By using a linked server your application will only need to connect to one SQL Server instance to retrieve data from multiple SQL Server instances. On that single SQL Server instance, you would define linked servers so your application could retrieve data from the databases that reside on a different SQL Server instance. Next time you are considering how to handle retrieving data from multiple instances of SQL Server from a single connection or single TSQL statement you might consider looking into using a linked server.

Eg:

SELECT e.* FROM SERVER2.AdventureWorks.Employee e inner join 
 Server1.AdventureWorks.Salary s on e.employeeid = s.employeeid

Performance of Distributed Query:

when you run a distributed query using a linked server, the query is processed locally. This may or may not be efficient, depending on how much data must be sent from the remote server to the local server for processing. Sometimes it is more efficient to pass through the query so that it is run on the remote server. This way, if the query must process many rows, it can process them on the remote server, and only return to the local server the results of the query

When running distributed queries on a linked server, if the linked server has the same character set and sort order (collation) as the local SQL Server, then you can reduce overhead and boost performance if you set the SP_SERVEROPTION "collation compatible" option to true. What this setting does is tell SQL Server to assume that all columns and character sets on the remote server are compatible with the local server. This same option can also be turned on for a linked server using Enterprise Manager or Management Studio.


References:http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

Read More

Fulltext search in sql server

To set fulltext search in sql server follow below steps:

EXEC sp_fulltext_database 'enable'
go
CREATE FULLTEXT CATALOG mycatalogName
go

CREATE NONCLUSTERED INDEX primaryKeyIndexName
    ON FAQ (pkColumnName);

CREATE FULLTEXT INDEX ON dbo.TableName
(
Question
Language 0X0
)

KEY INDEX PK_TableName ON mycatalogName
WITH CHANGE_TRACKING AUTO

Solution By:Rajesh Rolen

Read More

Truncate Database/ Delete data from all tables of database

-- disable all constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'

-- enable all constraints
exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Read More