SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...


What is Stored Procedure and How to write Stored Procedure

Stored Procedure (SP, here after I will refer it as SP) is as its name indicates stored in a database . SP contains one or more then single T-SQL in it, and it is re-usable. The beauty or advantage of SP is, it will get complied and stored in Database, now when ever you required to run the T-SQL or bunch of T-SQL you don't need to write it and compile it again, just call that Stored Procedure(SP).
Whereas in case of individual T-SQL it will get complied every time you run them; so it helps to reduce your time writing T-SQL, it saves CPU and I/O times it would take to compile and to create execution plan.

There are some advantages of using SPs, they are:
1. You do not need to rewrite your T-SQL which you would required to run often
2. It will get complied and stored in DB, and this will helps you saving your CPU & I/O cycle. * In case if some modification is done in SP you would required to re-compile it.
3. Can accept parameter as input value and will return output based on it
4. You can call one SP in another
5. You can have security configured for them, i.e. can grant or deny exeecute permission for particular user/role/group
6. SP which has parameters can help you protect from SQL Injection Attack
7. SP will help you reduce your network round-trip and hence network traffic

Here are some basic instruction and input on how to write SP(s).
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx http://www.sql-server-performance.com/articles/dev/create_stored_procedures_tsql_p1.aspx


should I shrink my database

I have come across to the post "should I shrink my database" many times, this is certainly not recommended unless it is required. When we have ample space on the hard drive let it be in the size it is.

Because what happens is when you shrink it and as time passes by it would required to grow again and this time it again involves your CPU and I/O cycle which would be costly affair; again, herealso there may be counter arguments too but this is what certainly not recommended on other hand it *depneds* on case to case bases.

What we should really do is to investigate the reasons, why it has grown that big (if you feel so) for example
1. Auto growth option,
2. Activities like BCP/Bulk insert,
3. Reindex,
4. defregment of database

Please refer Tibor Karsazi's article here on the same topic http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Hope this would helps.


New KB Articles on SQL Server 2005 and SQL Server 2008

Here are link of new KB Articles released on SQL Server 2005 and SQL Server 2008
List of Builds that are released after SQL Server 2005 SP2
List of Builds that are released after SQL Server 2008

FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order http://support.microsoft.com/kb/926292

Distribution Agent do not skip error 20598 SQL Server 2008 http://support.microsoft.com/kb/956376

The syntax for '' is incorrect in SQL Server 2005 Analysis Services http://support.microsoft.com/kb/956039


SQL Server 2008 - download November CTP now!!!

SQL Server 2008 November CTP is available for download, click here to grab your copy of SQL Server 2008

Here are some more resources on SQL Server 2008

SQL Server 20008 Product Overview

SQL Server 2008 Videos

SQL Server 2008 White Papers

SQL Server 2008 Webcasts

Happy Learning all :-)


MVP Again - New year gift from Microsoft

I got my MVP award renewal for the 2nd year, this has been a wonderful gift on New Year.