Counter of Festivals

Ashok Blog for SQL Lovers

Tuesday, 4 August 2015

Static SQL VS Dynamic SQL and Diffrence between Static SQL and Dynamic SQL

Static SQL VS Dynamic SQL:

Static SQL is SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries. Thus, the SQL statements cannot be hard-coded into the application.

Static SQL provides performance advantages over dynamic SQL because static SQL is preprocessed, which means the statements are parsed, validated, and optimized only once.

Static SQL  is compiled and optimized prior to its execution; dynamic SQL is compiled and optimized during execution.

Types of Upgrade/Migration in SQL Server?

SQL Server in-place vs side-by-side upgrade:

In an in-place upgrade, you stick in a CD, run the setup file and choose the upgrade option. The setup program will automatically upgrade older version of SQL Server to the new version and the older instance of SQL Server is replaced.
In a side-by-side upgrade, you install a separate instance of SQL Server(either on the same box or on a different one) and then move all the content(Databases, users/logins, etc) from the the older instance of SQL Server to the new instance.
What’s the best method to use for upgrade: Well, there is no “best” method, there is however, a “right” method. If you don’t have a separate hardware for a clean SQL Server 2008 install or if you can’t afford to install SQL Server 2008 as a separate instance on your current production box due to performance issues, your only option is to perform an in-place upgrade. Please note that an in-place upgrade is an all-or-nothing approach and so is more risky than a side-by-side upgrade. However, if you have a separate server, a separate clean install would always be better as it will not affect the current production instance (there by providing greater availability) and you will have plenty of time to test your stuff before going live with the new version.
Note: Regardless of which method you choose to upgrade, be sure to run Upgrade Adviser to find out the bottlenecks that might affect your upgrade. It also provides links to documentation that will help you resolve any issue that you might come across during upgrade. Download upgrade adviser here

Tuesday, 7 July 2015

To Send an DB Email from non-sysadmin user in SQL

Send an Email from non-sysadmin user in SQL

If user role have DB DatabaseMailUserRole  in the MSDB but still he cant send an Email through dbmail from databasemail  then u get 

SQL Database Mail Profile Name is Not Valid 

This problem occured when I was trying to send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 
For Example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile'
, @recipients = ''
, @subject = 'Automated Test Results (Successful)'
, @body = 'The stored procedure finished successfully.'
The error was:
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 141
Profile name is not valid
You can see that the non-sysadmin user has been added to the DatabaseMailUserRole role in the database.
Database Role Membership
Note: If the database mail profile is not set for public access then the user is not allowed to use the specified profile.
Now set the profile to Public.

Right-click on the Database Mail in the Management Studio and select the Configure Database Mail menu option.
Configure Database Mail in SQL
Click the "Next" button.
Database Mail Configure Wizard
Select "Manage profile security" then click the "Next" button.
Configuration Task in Database Mail
Now go to Public Profiles, select your profile name and then set the profile to Public then click the "Next" button .
Public Profiles in Database Mail

Note: The Manage Profile Security screen allows you to set this profile to either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (Microsoft database) to send e-mail using this profile.
Now click the "Finish" button.
Manage Profile Security in Database Mail
Now you can send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 2012.

Tuesday, 23 June 2015

Diffrence between Full Backup vs Differential Backup vs Transaction Log Backup

Full vs Differential vs Transaction Log Backup:

SQL Server provides three backup options - Full backup, Differential backup and Transaction Logs backup. Today, we will review the three backup strategies and key differences between them.

Full Backup
Full backup is just that, a full backup of your database at a point in time. You can restore the full backup on the same or a different SQL Server.

Differential Backup
Differential backup backs only the changes since the last full backup. The benefit of differential backup is that it is very fast and takes less space since you are only backing up the changes.

Differential backup has a backup chain which starts from the last full backup. All differential backups are from the previous full backup. It is possible to take another full backup without breaking the differential backup chain, i.e. to have it continue from the previous full backup. If you take a full backup with COPY_ONLY option, you will not break the differential backup chain, but without the COPY_ONLY option, the previous differential backup chain will be broken and a new chain will start from the most recent backup.

You can perform a restore at point in time by restoring a full backup and then applying the most recent differential backup.

Transaction Logs
Transaction Logs are the changes since the last transaction log backup. I have seen some confusion about whether transaction log backups are from the last full backup or from the last transaction log backup. If you are taking full database backup for the very first time, you transaction log back up chain will start after the full backup. Any subsequent full or differential backups will not break the log chain and the next transaction log backup will be from the last transaction log backup and not the last full backup.

The transaction log backup only works in Full and Bulk Logged recovery model and the only way to break the log chain is by either switching the recovery model to Simple or if you choose to override existing backup set when creating a full backup media set.

If your database is set to full or bulk logged recovery model, you must take frequent log backups otherwise your log file won't truncate, filling up your hard drive.

It is good to use all three backup schemes in your database environment to ensure you keep the restore media set or files as current as possible, so you can restore to a point in time and minimize data loss.

Differential backup:

differential backup offers a middle ground by backing up all the files that have changed since the last full backup. That is where it gets its name: it backs up everything that's different since the last full backup.
In the image below you can see an example on how a differential backup would look like for a backup job that runs four times:
    • differential backup
Restoring a differential backup is a faster process than restoring an incremental backup because only two backup container files are needed: the latest full backup and the latest differential.

Backup4all uses the information it has recorded in its catalog file (.bkc) to determine whether each file has changed since the last full backup.

Use differential backup if you have a reasonable amount of time to perform backups. The upside is that only two backup container files are needed to perform a complete restore. The downside is if you run multiple differential backups after your full backup, you're probably including some files in each differential backup that were already included in earlier differential backups, but haven't been recently modified.

  1. Restore is faster than restoring from incremental backup
  2. Backing up is faster than a full backup
  3. The storage space requirements are lower than for full backup
  1. Restore is slower than restoring from full backup
  2. Backing up is slower than incremental backup
  3. The storage space requirements are higher than for incremental backup

Tuesday, 16 June 2015

To resolve Deadlock other than Nolock hints

Please do not use NOLOCKs blindly. NOLOCKS results in dirty reads which can become a problem for your application. NOLOCKS are suggested when the tables thay are used against are seldom changed. If your tables have frequent transactions on them, NOLOCKS are not recommended.
To help minimize deadlocks, you can use any of the below suggesstion-
  1. Access objects in the same order.
  2. Avoid user interaction in transactions.
  3. Keep transactions short and in one batch.
  4. Use a lower isolation level.
  5. Use a row versioning-based isolation level. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning. OR. Use snapshot isolation.
  6. Use bound connections.
Check this link for further details in achieveing this.

Different Types of LOCK in SQL Server

Different Types of LOCK in SQL Server


Locking Hints and Examples are as follows. The usage of them is same same but effect is different.

Use row-level locks when reading or modifying data.
Use page-level locks when reading or modifying data.
Use a table lock when reading or modifying data.
Use a database lock when reading or modifying data.
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.


Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.
UPDATE Products WITH (NOLOCK)SET ProductCat 'Machine'WHERE ProductSubCat 'Mac'

Friday, 12 June 2015

After Server Restarted Tempdb Added NDF file Would Exists or Not?

After Server Restarted Tempdb Added NDF file Would Exists or Not:

Suppose you have Tempdb 1 MDF ,1 LDF, 1 NDF  then  If you added 2 NDF file for performance Improvement Purpose then After Server Restarted would those Added NDF file Exist or not.

After added Server Restarted as See below

but it is still exists 2 Added NDF file as see below