Technology with Vivek Johari

Please Vote for my article "SQL Triggers - An Introduction" on CodeProject.

2019-01-09T13:06:00.005+05:30

Please Vote for my article "SQL Triggers - An Introduction" on CodeProject.

One of my Code Project article on SQL Trigger has been selected as entry in this contest.
I have explain the concept of SQL triggers on it. Please have a look on my article and if you like it, Please VOTE for it.
Article:- SQL Trigger - An Introduction - Vivek Johari

CodeProject Link for Voting

Point In Time Restore for Azure SQL Database

2019-01-09T00:53:00.000+05:30

Point In Time Restore for Azure SQL Database

This article try to explain

Automatic backups services provided by Azure
Backup retention periods
How to take the point in time database restoration

This database restoration process help in protecting the database from corruption and more importantly from accidental deletion of data.

Read the full Article here

Watch Video Tutorial

Merry Christmas

2018-12-24T23:24:00.003+05:30

Wish you all a Merry Christmas and may this festival bring abundant joy and happiness in your life!

From

Technology with Vivek Johari

SQL Triggers – An Introduction

2018-12-08T20:12:00.000+05:30

This article will give you the detailed knowledge about the SQL Triggers which can be very helpful in your work. It will gives information about triggers & its various types, Magic tables, difference b/w trigger & stored Procedure, finding, enabling & disabling triggers, advantages & disadvantages of triggers with the help of many examples.

Read Full Article

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

2018-11-25T19:21:00.000+05:30

In this article we learn migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool. In this article, I have explained this as a step by step process

Read Full Article

Azure Tutorial

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database to Azure SQL Database using SSMS Deploy

2018-11-19T18:39:00.000+05:30

How to migrate SQL Database to Azure SQL Database using SSMS Export/Import

Migrating SQL Database to Azure SQL Database using SSMS Export/Import

Introduction
Creating the .bacpac file using SSMS Export
Importing the .bacpac file using SSMS Import
Summary

Introduction

There are many ways of migrating the On premise SQL Database to Azure SQL Database. In the previous article on migrating SQL Database to Azure SQL Database, we discuss the SSMS deploy method as a step by step process. This article will tell how to migrate SQL Database to Azure SQL Database using SSMS Export/Import data as a step by step process. It is a multi steps process where in first step we need to export the data into a BACPAC (.bacpac ) and then in next step, import that BACPAC file. It means even if the import data step is failed, we doesn't need to repeat the data export process again. As we have have the exported BACPAC file, we just need to restart the Import process. This process require longer downtime. So this process is suitable for larger databases if the downtime duration is acceptable.

Migrating SQL Database to Azure SQL Database using SSMS Export data

Creating the .bacpac file using SSMS Export

As a first step, we need to create the .bacpac file of the On premise SQL database which we want to migrate to the Azure SQL Server. So connect to the On premise database server and select the SQL database to migrate. Right click on it and select the option Export Data-tier Application.. (as shown in the below screenshot)

Select the database for migrating to Azure

Once the option Export Data-tier Application.. clicked, it will open the Export Data-Tier Application page as shown in the below screenshot.

Introduction Screen for Data export

Click on Next button to reach the Export Setting screen. In this screen, we need to select the location for storing the .bacpac file.

Select the export (.bacpac) file location

Click on the Next button to reach the Summary Page. In this screen, we need to recheck the setting which we have specified.

Verify the specified settings

After verifying the specified settings, click on the Finish button. It will start the process of .bacpac file creation as shown below.

Processing the creation of .bacpac file

Once this operation got completed, below screen comes. Click on the Close button. This will complete the database export part of this migration process.

Operation Completion Screen

Importing the .bacpac file using SSMS Import

After creating the .bacpac file by exporting the data, we need to import it into the Azure SQL Database. For this, first connect with the Azure SQL Server through SSMS. Then right-click on the Databases node and choose Import Data-Tier Application..

Choose Import Data-Tier Application.

It will open the below Import Data-Tier Application.. page as shown below.

Import Data-Tier Application..

Click Next button. It will open the Import Settings page. In this page we need to specify the .bacpac file location for import.

Specify the BACPAC file for import

Click Next to reach database setting page. Here we need to specify the Azure SQL Server Name, New Azure SQL Database name & the service tier based on the database size and other requirement as shown in the below screenshot

Database setting page

We can change the default Microsoft Azure SQL Database settings as per your requirement. For example, I have changed the Edition of Microsoft Azure SQL Database, Maximum database size (GB) and Service Objective for my database as shown in the below screenshot.

Change Azure SQL Database default settings

Once all database settings defined, click on the Next button. It will bring up the Summary page as shown below.

Summary Page for verify settings

Once settings verified, click on the Finish Button. It will start the Import process as shown in below screenshot.

Importing Database...

Once the Importing database process completed successfully, it will bring up the Result page. Click on the Close button.

Result Page - Importing Database process successfully completed

Database is successfully imported to the Azure SQL Database. We can it verify by connecting the Azure SQL Database using the SQL Server Management Studio and then expanding the Database node. Database will be visible and it is available for SQL queries as shown in the below screenshot.

Executing SQL Query against the imported database[/caption]

Summary

This article is the part of the series Migrating SQL databases to Azure SQL Database. In the last article in this series, we discussed SSMS deploy method for migrating the SQL Database to Azure. In this article we discussed how to migrate SQL Database to Azure SQL Database using SSMS Export/Import data as a step by step process. Unlike SSMS deploy method, which is a single step process and suitable for smaller database, it is a multi step process and suitable for large database but with longer downtime required. In the next article, we will going to discuss the migration of SQL database to Azure SQL Database using the Microsoft database migration assistant (DMA) tool.
Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com. If you want you can also join our Facebook community here or follow me on Twitter

Migrating SQL Database to Azure SQL Database using SSMS Deploy
Understanding Azure SQL Database- Introduction

Azure Pricing Models and Service Tiers

Azure – Creating an Azure SQL Database
How to connect with Azure SQL Database

Blog trasfering to new hosting server

2018-04-10T16:54:00.000+05:30

Hi Friends,

This blog is now transferring to different hosting server. So you may got some issue in accessing it. Sorry for the inconvenience caused

SQL Script to find the database & their files details on a SQL Server

2018-03-30T19:31:00.001+05:30

Sometimes we need a SQL Script which can gives the below information about the databases created on a SQL Server for house keeping purpose: -

1) Database Name
2) Database ID
3) Database Files name with their physical location
4) Database Creation Time
5) Users access type for Database
6) Database recovery model
7) Database State (ON/OFF)
8) Database Collation
9) File size on the Disk

Following SQL Query will help you in finding the above mentioned information for all databases on a SQL Server

SELECT
sd.[Name] AS [database Name]
,sd.database_id [Database Id]
,mf.name as [File Logical Name]
,case
when type_desc = 'LOG' then 'Log File'
when type_desc = 'ROWS' then 'Data File'
Else type_desc
end as [File type]
,mf.physical_name [Physical Location]
,sd.create_date [Database Creation date]
,sd.collation_name [collation name]
,sd.user_access_desc [User Access Type]
,sd.recovery_model_desc [Database Recovery Model]
,sd.state_desc [Database State]
,size_on_disk_bytes [File Size in bytes]
,size_on_disk_bytes/ 1024 as [File Size in KB]
,size_on_disk_bytes/ 1024 / 1024 as [File Size in MB]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
inner join sys.databases sd on fs.database_id=sd.database_id
JOIN sys.master_files AS mf ON mf.database_id = fs.database_id
AND mf.file_id = fs.file_id
ORDER BY sd.database_id

I hope that above query will be helpful to you. Don't forget to give your valuable comments on this article. Also you can send me your valuable feedback on my email id askvivekjohari@gmail.com

SQL Script: - How to find the database restoring history of SQL Server databases

2018-03-30T15:38:00.001+05:30

Sometime we need to find out the history of database restoration to get the answers of following questions: -

1) Do we need to restore the database again as there can be a possibility that it is restored recently?
2) Which backup file (with location information) is used for database restoration?
3) Name of the source database whose backup file is used database restoration?
4) When database backup file is created ?
5) Which user perform the database restoration activity?

Below query can be used for finding the database restoration history

SELECT [r].[destination_database_name] as [Database Name],
[r].[restore_date] [Restore Date],
[r].user_name as [User],
(cast([b].software_major_version as varchar(10))+'.' +
cast([b].software_minor_version as varchar(10)) +'.'+
cast([b].software_build_version as varchar(10))) as [Server version],
[b].compressed_backup_size as [Backup file Size],
[b].[backup_start_date] [Backup Start Time],
[b].[backup_finish_date][Backup End Time],
[b].[database_name] as [Source Database Name],
[bkpmf].[physical_device_name] as [Backup File Used For Database Restore]
FROM msdb..restorehistory r
INNER JOIN msdb..backupset b
ON [r].[backup_set_id] = [b].[backup_set_id]
INNER JOIN msdb..backupmediafamily bkpmf
ON [b].[media_set_id] = [bkpmf].[media_set_id]
ORDER BY [r].[restore_date] DESC

For example, suppose we created a database name as "Sampledatabase" which is never restored. Then if we execute the above query we got no restoration history of this database:-

Now we take backup of this database

and restore the "Sampledatabase" with the backup taken in the above step.

If we again execute the SQL Script to get database restoration history, we got the following result

Now if we take another backup with the different name as "SampleDatabase_1.bak" on the same location and tried to restore the database again, then one more entry for the Sampledatbase will come on the execution of the SQL query for getting Database restoration history as shown in below images

If we see the outcome of the query, we also got the version of the SQL server on which database backup file is created & the size of the backup file. Sometime these additional information can become handy.

I hope database guy will get help from this query. Please send me your valuable feedback on askvivekjohari@gmail.com. Thanks for visiting my blog.

SQL Script: - How to find the last access date of a database

2018-03-29T17:28:00.001+05:30

Sometime we create databases on Test/ Development database servers for temporary uses for short duration and forgot to delete them after the work got completed. As a part of house keeping activity, we need to find out when a database is last accessed in order to find out the databases which are not used since long time and can be a deleted.

Below mentioned SQL query will going to help you in finding out the last access date of all the databases of a SQL server

SELECT name as [Database Name], [Last Access Date] =(select MAX(temp.lastaccess)

from ( select lastaccess =

max(last_user_seek)

where max(last_user_seek)is not null

union all

select lastaccess = max(last_user_scan)

where max(last_user_scan)is not null

union all

select lastaccess = max(last_user_lookup)

where max(last_user_lookup) is not null

union all

select lastaccess =max(last_user_update)

where max(last_user_update) is not null) temp)

FROM master.dbo.sysdatabases sysdb

left outer join sys.dm_db_index_usage_stats Idxus

on sysdb.dbid= Idxus.database_id

group by sysdb.name

Understanding Azure SQL Database- Introduction

2018-03-19T00:15:00.002+05:30

Introduction:-

In this first article of the series Understanding Azure SQL Database in which I am going to publish in the coming weeks to understand Azure SQL Database concepts. After this series, you will have the good understanding of the following Azure SQL concepts

What is Azure and Azure SQL Database
Pricing Model of Azure SQL
Creating SQL Server & Database on Azure and how to connect them
Understanding of different Azure Service Tier
Difference between Azure SQL Database & SQL Server
Security features in Azure SQL Database
Database Transaction Units (DTU) & how to calculate it.
Elastic Pool in Azure SQL Database
Database Backup & Restoration in Azure SQL Database
High Availability & Disaster Recovery feature in Azure SQL Database
Database Migration from SQL Server to Azure SQL database
Database Migration from Azure SQL Database to SQL Server
Azure Metric Alerts and how to create them
Performance Tuning in Azure SQL Database

When we think of Azure SQL Database, first question comes in our mind is what is Azure? Microsoft Azure is a cloud computing platform created by Microsoft to build, test, deploy and host the applications using the global network of Microsoft managed data centers.

Next question comes, what is Azure SQL Database? Azure SQL database can be defined as the Database as a Service offered on Microsoft Azure. By Database as a Service means that Microsoft as the service provider will going to manage the database software, taken care of Database backup, recovery, High Availability, Operating System. We as users only need to taken care of database and the queries running inside the database. Also we need to pay as per our current database uses and nothing to pay anything upfront. Also we can scale up or scale down or create or destroy the environment as per our requirement without any interference from the provider Microsoft.

Azure provides three Service tiers for setting up the SQL Database. These 3 Service Tiers are:-

1) Basic Service Tier
2) Standard Service Tier
3) Premium Service Tier

Within Standard & Premium Service tiers we have different level. For example in Standard Service tier we have 4 levels S0 , S1, S2 & S3. In Premium Service tier, we have 6 levels P1, P2, P4, P6, P11 & P15.

Azure SQL Database Backup Retention time period, Performance, Database Size are depend on which Service Tier we choose at the time of database creation. We can change the service tier or service tier level any time as per application requirement. We will going to know more about Service tiers in the coming articles in this series.

Pricing of Azure SQL database is calculated on the hourly bases and it depends upon which Service tier we have used. The hourly rate is determine on the basis of highest Service tier we used in that hour.

In case of SQL Server hosted in a VM, users are responsible for maintaining the operating system, taking regular database backups, database disaster recovery planning, maintaining the SQL Server, SQL performance & Security. But in case of case of Azure SQL database, we just need to choose the correct Service Tier as Azure itself responsible of taking care of operating system, Backups, High Availability & Security.

There are many features of Azure SQL database, which we are going to cover in the upcoming next articles of this series. Till then keep learning and keep visiting this site. Don't forget to send your valuable feedback as comment or you can send to my mail id askvivekjohari@gmail.com

Learn How to Recover Deleted Records from SQL Server 2014

2017-08-08T17:59:00.000+05:30

Problem

Sometimes users may have numerous of databases in their SQL Server. However, while managing their database sometimes by mistake their records are deleted due to which they face a lot of issues. To maintain the workflow they need to recover deleted records from SQL Server 2014. This could be possible only with successful SQL LOG Recovery

How to Get Back Deleted Records in SQL Server: Solution

Mainly Transaction log is responsible for all database-logging activities. Similarly, in this discussion, we will utilize the usage of similar undocumented function "fn_dblog" to get any unauthorized deletion of data as well as to retrieve deleted data in SQL server.

If you are aware that when your data is removed then, you can easily solve your problem on how to recover deleted data in SQL server database. However, if you do not know the data as well as the time when the data is erased then, it is important to understand first who has deleted the data. In this segment, we will first find the particular LSN under which deleted statement is run after that we will rollback deleted records in SQL until that LSN.

Each record in MS SQL Server transaction log is distinctively recognized by LSN (Log Sequence Number). LSN of log record at which particular event happened can be beneficial for making precise restore sequences and to retrieve deleted data. LSNs are utilized internally at the time of RESTORE sequence to path the point in time at which the data has been reinstated. When data backup is restored, data is reinstated to LSN corresponding to point in time at which backup was taken. Differential, as well as log backups, advance restored the database to later time that corresponds to higher LSN.

Important: Do not do any modifications in production without testing it properly in lower-life cycle environments

Recover Deleted Records from SQL Server with LSN

Follow these steps to recover deleted records in SQL Server with successful SQL Log Recovery:

Step 1

Before moving further, we will make the database as well as the table on which one will run DELETE statement. Then, we will get the data deletion and then rollback deleted records in SQL. Implement the T-SQL code given below to generate database and table and retrieve deleted data.

Step 2

We have made a database with table 'Location' of three columns. Now, we will put few rows in table.

Step 3

We will go further to erase some rows and recover deleted records from SQL Server table by using LSNs from the SQL Server Transaction Log.

Step 4

Now, find the SQL Server Transaction Log and retrieve deleted records in SQL. Run the mentioned code below to get data information about all removed transactions with SQL Log recovery.

You can see the transaction IDs under which DELETE statement was executed. As we know that data is removed from Location table, we will focus on only those transaction IDs under which data is erased from location table to retrieve deleted data. We can get table name from "AllocUnitName" column. It states DELETE statement has been executed on HEAP table 'dbo.Location' under transaction ID 0000:0000055e. There is only one transaction ID under which we can view deleted records SQL Server, which means this action has been implemented in a single batch. Now, capture transaction ID from next command.

Step 5

We initiate that transaction ID from above script that we will utilize in below statement to find the LSN of LOP_BEGIN_XACT operation and recover deleted records from SQL Server. It means that we want an exact LSN when DELETE statement started its transaction to retrieve deleted data.

Step 6

We will utilize STOPBEFOREMARK operation to get back deleted records in SQL Server. We cannot utilize this hexadecimal value to implement the operation of STOPBEFOREMARK. Therefore, we want alteration this value into decimal format. Then put all these together as single number.

Step 7

Now implement transaction log backup on database where data was removed if transaction log backup has not competed since data deletion. Then, we will restore the database to other location or on a similar server with the variant name until above LSN. Then, we will import and recover deleted data in SQL Server from newly reinstated database to production database.

Step 8

Now, the database is restored, One can check whether you could recover and find deleted records SQL Server or not. Execute the below SELECT statement to preview the first ten rows of location table that was deleted and then retrieve deleted data.

Now you know how to retrieve deleted records in SQL Server with SQL Log Recovery. You can utilize this erased data as per your need. If you need to export this data to live database you can utilize the SQL Server Integration Services, i.e. import and export wizard.

SQL Log Recovery become Hard

Above steps can be useful in most of the cases. But several situation may occur when it becomes difficult to restore deleted data with SQL Log recovery. Lets take a look on these situation:

Changes made after the DELETE, TRUNCATE or DROP statments
Unable to take the SQL Server offline
New Transactions performed after the data was lost
No, recent Full Database backup available.

In such situation user can go with third party SQL Log Recovery to successfully restore deleted data records in SQL Server.

Conclusion

After understanding, the users problem how to recover deleted records from SQL Server 2014. In the above discussion, we have discussed the ways to retrieve deleted data from the SQL Server database by Successful SQL LOG Recovery that makes easy for users in restoring SQL Server deleted records in SQL version 2016 / 2014/ 2012 / 2008r2 / 2005 / 2002 and all below version.

Two Ways to Restore Database from MDF File in SQL Server

2017-07-27T18:21:00.000+05:30

Introduction

In todays decade Relational database Management system became the necessity of the organizations whether they are small level or large level. In such context SQL Server is the best and successful RDBMS which provides highly integrated services to manipulate the SQL database over the Server. As we talk about the SQL Server database the Server provides the primary storage call MDF File, NDF file also known as secondary file, LDF log transaction file. Well, in this section we will discuss about how to restore LDF and MDF files to a database so lets make some prerequisites perform tasks without any trouble.

First of all detach the database. If you are trying to attach the database which is not detach you will get an error
Maintain MDF File and LDF file in a specific folder before going to attach
Locate MDF File and LDF File in the same location

There are two ways to restore database from MDF file in SQL Server:

With the help of SQL Server Management Studio
Using T-SQL

1. Through SQL Server Management Studio follow some steps:

Open SSMS and go to object explorer of the database engine instance
Then make a right click on the database and select Attach option of the list button
Windows appers, then click on Add button
Select the .mdf file and click on OK Button
Validate the selected files and go to OK Button

Now check the attached database

2. On using T-SQL

Login to SQL Server database engine
Go to New Query Window and use Create database with Attach close

    CREATE DATABASE MyDatabase 
    ON (FILENAME = 'C:\SQLServer\MyDatabase_Data.mdf'), 
    (FILENAME = 'C:\SQLServer\ MyDatabase _Log.ldf') 
    FOR ATTACH;

Conclusion

In the above section, we learned how to restore LDF and MDF files to a database. It is highly recommended that you have to detach the MDF file before attaching a new one. This is a very helpful method to restore database from MDF file in SQL Server. The second method performs same task using T- SQL, for the users who needs to attach MDF file through SQL script. In case, if the transaction log file is missing then you can still attach MDF file in SQL Server without LDF file and if the MDF file is not accessible or corrupted then you can repair MDF file using third party SQL Recovery tools.

How to Resume Suspended Database Mirroring in SQL Server ?

2016-09-03T17:42:00.000+05:30

Overview

Database mirroring is a feature in SQL Server which is creating and maintaining the redundant copies of the database. The purpose behind database mirroring is continuous data availability and minimizes the loss of the data. Redundancy ensures that there is always a one backup copy of the database should remain accessible at the time of updates.

Basically mirroring feature in SQL Server is able to stand the random failures at the several parts when the user is linking between the two servers. It has some in built techniques which are not affected to the outside world. SO the SQL Server user can reboot the servers, connect and reconnect to the link and after that the database mirroring can still be resumed afterwards.

Causes for Database Mirroring Pause

No storage space in the primary storage.
SQL Server not working properly.
The main database is not connected to the mirrored database link.

So, an error is shown to the user while they are performing the database mirroring and all of the sudden it pauses.

Methods to Fix Database Mirroring Pause?

The above error occurs when the mirror server is running out of the space. The SQL Server user will be able to resume the mirroring as soon as the user frees some space. It means that there is no storage left in the primary storage, so in order to resume the database mirroring the user will check if they have any space in the secondary storage then they will free up all the primary storage and store it to the secondary database in order to resume the database mirroring.

Steps to Resume the Database Mirroring In SQL Server

Here are the steps to resume database mirroring using SQL Server Management Studio:

First, at the time of database mirroring connect to the principal server instance inside Object Explorer and then click on the server name to expand the server tree.
Second, expand the databases and click on the database.
Third, now right click on the database and select Tasks and after that click on Mirror. It will open the mirroring page of the Database properties dialog box.

Steps to Resume the Database Mirroring Using Transact-SQL

Connect to the database engine of the either partner.
Click on New Query for the standard bar.
Now Enter the following Transact-SQL query in order to resume the database mirroring:

ALTER DATABASE testdb SET PARTNER RESUME;

By the use of this command the user can resume the database mirroring when it was paused.

Conclusion

We discussed what is database mirroring and why it is important. In addition, we discussed about what are the causes for database mirroring pause and gave an example for it. We also gave some steps to resume database mirroring using any one of the two platforms. Database mirroring is an important thing as it backups the copy of the database and can be maintained easily at the time of loss of the data or loss of the database file.

Happy New Year Friends

2016-01-01T21:28:00.000+05:30

Wishing all of you a very Happy New Year. May GOD bless all of you with lots of Success and Happiness.

SQL Recovery Software: An Ultimate Tool For SQL Database Recovery

2015-12-31T17:36:00.000+05:30

SQL Server is a database management system which enables to store and retrieve data from the database. Due to some reasons like virus infection, improper closing of the Server, etc. the data in the database may go damaged or corrupted. The recovery of the data can be made possible with SQL Recovery Software. The tool attains an easy recovery of the database files from corruption or damage; no matter how deep the infection is. Both the MDF and NDF files get repaired using the tool and all the triggers, stored procedures, tables, functions, etc. in the database are also recovered within seconds.
More On Software
SQL Repair tool restores all the data from the damaged database file of any server such as; Server 2016, 2014, 2012, 2008, & all below versions. With the software, all the hard deleted data items can be restored without any changes to the data.Being a Windows based software, the tool supports all the versions; Windows 8.1 and below.The Advance scan option recovers entire data when the corruption infection is severe and there is Quick scan mode for minor damage recovery. The tool is supported with the auto-detection option for the selection of NDF files as well as the SQL Server version. Once the restoration of the database files are done, software allows exporting the files back to the Server.
Overview Of SysTools SQL Recovery Software

Function	Recover SQL Database from corruption
Version	6.3
Size	6.5 MB
Server Support	SQL Server 2016, 2014, 2012, 2008 R2 / 2008, 2005 & 2000
Windows Version	Supports Windows 10 and below

Outstanding Features of SQL Recovery Software

Scan Modes
The tool is implemented with advanced algorithms for the recovery of the files. The “Scan Mode” option helps to recover the corrupted or damaged Server database files. The tool supports two scan modes; Quick and Advance. If the database file is less corrupted, users can go for “Quick” scan mode or else if the damage is severe, “Advance” scan restores all the data without loss.

Detects Server Version
Suppose, if you don’t know the version of the SQL Server where the database file is saved, there is “Auto detect SQL server file (.mdf) version” choice which will automatically search for the server file version once when the checkbox is ticked. Else, the user can mention the server version directly from the radio options provided but, one should be careful while opting since wrong selection of the server may lead to inconsistency.
NDF File Scan Option
The users can either add multiple files or folders for scanning and recovering. If the added files are to be removed, the tool is sufficient with that feature too. The eminent feature of the NDF option is that, it automatically detects the NDF files when the “Autodetect the SQL server secondary database (.ndf) files” option is selected. If the user doesn’t know the location of the file, auto-detect feature will be of useful; both to search the file and save time. Else, the user can select and provide the files using the “Let me choose the SQL Server secondary database (.ndf) files” choice

Hierarchical Structure
After the restoration of the database files, it shows the content of the files in hierarchical manner. The software will show all the contents such as; stored procedures, tables, triggers, functions, rules, etc. and each of the contents is expandable too. The structuring makes the viewing comfortable and easy.

Export Modes
Once when the files or folders are recovered from the damage, the tool allows exporting the recovered files back to the database itself. Either the user can export to “SQL Server Database” or to “SQL Server Compatible SQL Scripts”. If the user chooses the first option then, will have to provide the server name, database name, the credentials of the server. But, the latter option is usually opted when the server is not present or files are not to be exported to the server for time being. The “SQL Server Compatible SQL Scripts” choice will save the files in scripts and user can later transport the scripts.

Dual Export Option
While exporting the database files to the server or when saving in script forms, the tool facilitates the users with two options. User can export the files either “With only Schema” or “With Schema & Data” options. Both these options are applicable for both the exports i.e. to SQL Server Database or SQL Server Compactible SQL Scripts.

Versions & Its Extents
Demo Version
The capability of the application can be checked using the demo version available. If the user is satisfied will the working then, can go for the purchase option. But, the demo version is limited to the recovery of the files. The exporting and saving cannot be done with the demo version.
License Version
Along with the demo version, the full version is also available in the office website. The Licensed version recovers all the added files or folders and enables to export and save the restored files back to the server, if needed.
Pros & Cons
Pros:

Supports the recovery of both .ndf and .mdf files from damages or corruptions.
Enables to scan multiple .ndf files at a time.
Automatically selects the SQL Server version for the MDF files, if unknown.
Two scanning modes for the recovery of the files.
Selects the NDF files automatically, if the user doesn’t know the location.
Exports retrieved files back to Server database or saves in Compatible SQL Scripts.
Provides facility for exporting either schema only or both schema and data together.

Cons:
Doesn’t scan and recover LDF files of the database
The Verdict
In the technical point of view, the tool is equipped with all the sufficient features for the recovery of the database files. On the basis of its working, software can be rated as 9.6/10. Though the tool doesn’t recover LDF files, it efficiently restores both MDF and NDF files which are more important than the LDF files. For the repairing of the database files from damage, users can depend on the SQL Recovery Software without a second thought.

Happy Independence Day - Jai Hind

2015-08-15T14:12:00.001+05:30

Wishing you a very

Happy Independence Day

" We owe a lot to the Indians, who taught us how to count, without which no worthwhile scientific discovery could have been made. "Albert Einstein.

Some facts about India

Indus Valley Civilization is the world’s oldest civilization.So, India is the world’s oldest, most advanced and continuous civilization.
India has the world’s third largest active army, after China and USA but India has never invaded or attacked a country.
Today, India is the world’s third largest economy.
The Tirupati Balaji temple and the Kashi Vishwanath Temple both, receive more visitors than the Vatican City and Mecca combined.
Every 12 years, a religious gathering called the Kumbh Mela occurs in India. It is the world’s largest gathering of people.
Varanasi, also known as Benaras, was called "the Ancient City" when Lord Buddha visited it in 500 B.C., and is the oldest, continuously inhabited city in the world today.
India has third largest Muslim population in the world and has more mosques (300,000 mosques) than any other nation in the world.
Jews and Christians have lived continuously in India since 200 B.C. and 52 A.D. respectively
Two major religions, Buddhism and Jainism, were established in India.
Takshila is said to be the first every university in the world; it started around 700 BC.
Indian Railways employs more than 1.3 million people. That’s more than the population of many nations.
India is the largest democracy in the world. More than 54 crore people voted in the 2014 General Election – more people than the population of USA, UK, Australia and Japan combined.
India has the largest English speaking population in the world.
India’s space program is one of the top 5 space programs in the world and off course we invented water on the moon.
Chess was invented in India.
And discovered the number zero.
And the value of pi.
And trigonometry, algebra, calculus.
Ayurveda is the earliest school of medicine known to mankind. The Father of Medicine, Charaka, consolidated Ayurveda 2500 years ago.
Cataract surgery and plastic surgery are invented in India
Around the 17th century, India was one of the richest countries in the world.
India is one of the only three countries that makes supercomputers (the US and Japan are the other two).
India has the largest amount of vegetarians in the world. So much so that Pizza Hut had to open their first pure vegetarian restaurant in the country and KFC had to introduce a “vegetarian” menu for India.
India gave the world Yoga, that has existed for more than 5,000 years.
Martial Arts was first created in India.

Proud To Be An Indian

SQL Script to find all the triggers defined on a database or on a single table

2015-08-14T16:04:00.003+05:30

Sometime we need to find all the triggers defined on the database. So in this case, we can use the below SQL Query:-

SELECT
tbl.name as [Table Name]
, trig.name as [Trigger Name]
, trig.is_disabled
FROM [sys].[triggers] as trig
INNER JOIN sys.tables as tbl
ON trig.parent_id = tbl.object_id

In case, if we need to find out all the triggers defined on a particular table then we can use the below SQL script

SELECT
tbl.name as [Table Name]
, trig.name as [Trigger Name]
, trig.is_disabled
FROM [sys].[triggers] as trig
inner join sys.tables as tbl
on trig.parent_id = tbl.object_id
where tbl.name='Tblname'

/* where Tblname name is the name of the table*/

Related article

SQL Triggers - An Introduction

Keep learning and don't forget to gives feedback on the article. You can also send feedback to me on my mailid askvivekjohari@gmail.com

Interview question - Is Clustered index on column with duplicate values possible?

2015-08-13T12:33:00.001+05:30

Through this article, we are going to discuss three important interview questions of SQL which are given below:-

1) Can we create clustered index on a column containing duplicate values?

2) Can we create a Primary Key on a table on which a clustered index is already defined?

3) If a clustered index is already defined on a table and we create a primary key on this table, then is there any index is automatically created on the column on which Primary key is defined?

Many people say no for first question as they always consider clustered index and Primary key together and primary key can only be created on column with unique and not null values. Since primary key and clustered index combination is always consider as best combination like made in heaven so many people thought clustered index can only be created on the column on which primary key is created.

But this is not correct. We can create clustered index on the columns on which primary key is not defined or in simple words clustered index can be created on the non primary key column of a table. We know that when we defined primary key, a clustered index is created on the column / columns automatically by default. But if a clustered index is already defined for the table and we created the primary key latter then clustered index is not created on the primary key column as only one clustered index can be possible for a table which is in this case, already defined. So Primary key and clustered index are both different objects in SQL. Uniqueness is must in case of Primary key but it is not a must condition in case of Clustered Index.

For example,
Suppose we have a table called tbl_clustered whose structure is given below:-

create table tbl_clustered (Id int identity(1,1), Empname nvarchar(200 ))

Below is the data inserted query to insert data in the table tbl_clustered

Insert into tbl_clustered (Empname )Values ('Kumar'), ('Gupta'), ('Arora'),('Gupta'), ('Gupta')

Now let see the data inserted into the table using the below query

select * from tbl_clustered

Result:-

Id Empname

1 Kumar

2 Gupta

3 Arora

4 Gupta

5 Gupta

So till now, we have created a table and inserted some data in it. ID column is the identity column and Empname is varchar type column and we inserted some duplicate values in the Empname column.

Some people thought when we create an Identity column, primary key is automatically created on it. This is wrong. We usually create primary key on Identity column as it contains unique and not null value but primary key is not created on it by default unless we create it manually.

Let see the table structure to make sure that no primary key or clustered index is created on the table yet.

To see the table structure, we use the following command:-

sp_help tbl_clustered

Now lets create a clustered index on the table tbl_clustered.

create clustered index inx_clux_test on tbl_clustered( Empname )

Above command will create the clustered index successfully irrespective of the fact that Empname column contains the duplicate values. Let again check the table structure again

sp_help tbl_clustered

If you see the above picture, you can find the details of clustered index in the last row.

Now let create a Primary key on the table to show that if a clustered index is already created on a table and then we try to create a primary key then the primary key is created but without any clustered index.

let create the primary key using the below script

Alter table tbl_clustered add primary key ( id)

Let see the table details again to check is any clustered index is created on the primary key column when we defined the primary key in the table.

If we see the above picture, we got to know, if we create a primary key on table on which a clustered index is already defined, then a non clustered index is created automatically on the primary key column. So the table has two indexes

Clustered Index defined manually by us on the table intially
Non clustered index created automatically with the creation of primary key on the column on which Primary key is defined.

Summary:-Through this article, I tried to show that primary key and clustered index are the different objects. We can create a clustered index on the column which contains duplicate values but primary key can only be created on the column which contains unique and not null values. Also, if a clustered index is already defined on a table and we try to create a primary on the table then a non-clustered index is created on the column on which the primary key is defined(instead of clustered index). But we should try to design the database so that clustered index is created on the primary key as we mostly use primary key in Joins conditions. Also primary key is referred by the foreign keys. So clustered index on the primary key helps in increasing the database performance.

You can refers the following articles for better understanding of the concepts used in this article

For Primary Keys

Sql server Constraints - Primary Key, Foreign Key, Unique Key, Not Null, Check Constraints

Difference between Primary key and Unique key

For Indexes:-

Indexes in Sql server

Difference between Clustered Index and Non clustered Index

Keep learning and don't forget to gives feedback on the article. You can also send feedback to me on my mailid askvivekjohari@gmail.com

Question of the Month (July)

2015-08-03T15:20:00.002+05:30

Suppose we have a table emp_plan which contains 4 columns "Empid" (employeeid), Planid (Projectid), Startdate(Allocation Start date) and Enddate (Allocation Enddate). Its structure is given below:-

Create table emp_plan (empid nvarchar(200),planid nvarchar(20),startdate datetime, enddate datetime)

Also below is the script to enter sample data into the table:-

insert into emp_plan(empid,planid,startdate,enddate)

select '001','planA','2015-05-15','2015-05-30'

union all

select '001','planA','2015-05-31','2015-06-14'

union all

select '001','planA','2015-06-15','2015-06-30'

union all

select '001','planA','2015-07-10','2015-07-20'

union all

select '001','planA','2015-07-21','2015-07-30'

union all

select '001','planB','2015-06-14','2015-06-30'

union all

select '001','planB','2015-07-10','2015-07-20'

union all

select '001','planB','2015-07-21','2015-07-30'

union all

select '002','planA','2015-06-14','2015-06-30'

union all

select '002','planB','2015-07-10','2015-07-20'

union all

select '002','planB','2015-07-21','2015-07-30'

So the data contain in the table is given blow:-

Now if a employee has the same project(planid) with no gap in the next allocation segment( allocation segment means from allocation startdate to allocation enddate), then we need to join the continuous allocation segment. For example if you see the data in the table, in the first 3 rows, employee having empid 001 is allocated to the Same project (PlanA) and all allocation segments are continuous (from 2015-05-15 to 2015-06-30) as shown below:-

2015-05-15 to 2015-05-30 (1st row)

2015-05-31 to 2015-06-14 (2nd row)

2015-06-15 to 2015-06-30 (3rd row)

But the 4th row contains allocation segment which is not not continuous with the above 3 allocation segments as there is a gap between the 3rd row enddate and 4th row startdate and again 4th row is in continuation with 5th row with same employeeid 001 and project id (Plan1) as shown below:-

2015-07-10 to 2015-07-20 (4th Row)

2015-07-21 to 2015-07-30 (5th Row)

So expect result set should be

Empid Planid Startdate Enddate
001 planA 2015-05-15 00:00:00.000 2015-06-30 00:00:00.000
(1st to 3rd Row)
001 planA 2015-07-10 00:00:00.000 2015-07-30 00:00:00.000 (4th to 5th Row)

So according to the above rule, expected result from the sample data inserted into the table (as shown in the above picture), should be same as given below:-

Expected Result

Empid Planid Startdate Enddate
001 planA     2015-05-15 00:00:00.000     2015-06-30 00:00:00.000
001 planA     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000
001 planB     2015-06-14 00:00:00.000     2015-06-30 00:00:00.000
001 planB     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000
002 planA     2015-06-14 00:00:00.000     2015-06-30 00:00:00.000
002 planB     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000

So what should be the query/stored procedure to get the above result?

Do you know about Whatsapp web?

2015-07-11T15:45:00.000+05:30

Whatsapp claims that they currently have 500 million users worldwide. That is pretty impressive looking at the good amount of distinctively decent competitors they have within the space of Social Networking communication. Viber, Line, Google Hangout,Facebook Messenger & Hike (Commonly in India). While all these products are growing inch by inch and including new set of features into their product, whatsapp has never really seen any major makeover or a big release since Facebook took it over. Changing the paradigm, Whatsapp’s Web version has been introduced recently. Let us review and assess do we have got a Cherry with an already delicious pineapple cake or a bitter cup?

To begin using Whatsapp web, you would have to follow few steps

1. Update your whatsapp application to the latest version: Right now, the web version is only compatible with Google Chrome when paired with the Android, Windows Phone, or Blackberry version of the mobile app. iOS users sadly have to wait for some more time till they see the sunrise.

2. Pairing: The process of bringing whatsapp to the Desktop shall begin by pairing the Web version with the Whatsapp Mobile Application. To pair your mobile application, navigate to Web version of Whatsapp by typing in http://web.whatsapp.com on your browser’s Address bar. You shall view a similar interface as below

Now, open the Whatsapp application on your Android mobile and open Menu. You shall be prompted to scan the QR code present on the Chrome window (As depicted in figure above).

Note: QR code(abbreviated from Quick Response Code) is a complex matrix of coded information represented in a square shape. If you want to know more about QR code, head here.

Point your mobile so that QR Code can be seen within your mobile and fits within Scan area. The Mobile shall automatically pair the web version of whatsapp to load the similar view on web view.

Once you are in, you can search for your friends in the similar fashion as you’d do from your mobile. You can send messages, share images. You also have the option to record your voice using the Microphone button, that is placed exactly where you’d observe on your mobile version (Adjacent to message type-in area).

The only thing that I really miss is the ability of Web version to forward the received messages. Hopefully, whatsapp should be able to bring this feature soon. Till then, you can still enjoy the luxury of keep plugged on your desktop, without your boss knowing that you are still “Whatsapp Phantom“.

Original Source

Script to find complete months between two given dates

2015-07-10T23:28:00.001+05:30

Below script will help in finding the complete months within the 2 given dates. In the below script we takes below dates as example

Startdate =2015-07-05
Enddate =2016-01-06

/**********************Script Start**************************/

IF (object_id('tempdb..#month') is not null)
DROP TABLE #month

CREATE TABLE #month (id int identity(1,1),MonthStartDate DATETIME,MonthEndDate DATETIME)

DECLARE @startdate AS DATETIME
DECLARE @enddate AS DATETIME

DECLARE @monthstartdate AS DATETIME
DECLARE @monthenddate AS DATETIME

SET @startdate='2015-07-05'
SET @enddate='2016-01-06'

SELECT @monthstartdate= dateadd(mm,1,dateadd(DD,-day(@startdate)+1,@startdate))

SELECT @monthenddate= dateadd(dd,-1,dateadd(mm,1,@monthstartdate))

IF @enddate<@monthenddate
BEGIN

INSERT INTO #month
SELECT null,null
END
ELSE IF @startdate=@monthstartdate and @enddate=@monthenddate
BEGIN

INSERT INTO #month
SELECT @monthstartdate,@monthenddate
END
ELSE IF (@enddate>@monthenddate)
BEGIN

IF @startdate=@monthstartdate
BEGIN
INSERT INTO #month
SELECT @monthstartdate,@monthenddate
END

WHILE (@enddate>=@monthenddate)

BEGIN

INSERT INTO #month
SELECT @monthstartdate,@monthenddate

SELECT @monthstartdate=dateadd(MM,1,@monthstartdate)
SELECT @monthenddate=dateadd(day,-1,dateadd(MM,1,@monthstartdate))

END

END

SELECT cast(MonthStartDate as DATE) as MonthStart,cast(MonthEndDate as DATE) as MonthEnd FROM #month

/**********************Script End**************************/

This script will gives the following result:-

MonthStart MonthEnd
2015-08-01 2015-08-31
2015-09-01 2015-09-30
2015-10-01 2015-10-31
2015-11-01 2015-11-30
2015-12-01 2015-12-31

Script to find the complete weeks within two given dates

2015-07-10T22:55:00.003+05:30

Sometimes we need to find out the complete weeks within two given dates. Below script will help in finding the complete weeks within 2 given dates. In this script, I have used below dates as example

Startdate=2015-04-12
Enddate=2015-06-10

SET DATEFORMAT YMD
if (object_id('tempdb..#weekdays') is not null)
drop table #weekdays

Declare @StartDate as datetime
Declare @EndDate as datetime
Declare @Initialweekstart as datetime
Declare @weekstart as datetime
Declare @weekend as datetime
declare @day as int

set @StartDate='2015-04-12'
set @EndDate='2015-06-10'

Create table #weekdays(weekstart datetime, weekend datetime)

select @Initialweekstart=dateadd(wk, datediff(wk, 0, @StartDate), 0)

if @Initialweekstart<@StartDate
begin
set @weekstart= DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@Initialweekstart),0))
print @weekstart
set @weekend=DATEADD(dd,6,@weekstart)
end
else
begin
set @weekstart=@Initialweekstart
set @weekend=DATEADD(dd,6,@weekstart)
end
while @weekend<=@EndDate
begin
insert into #weekdays(weekstart,weekend)
values (@weekstart,@weekend)
set @weekstart= DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@weekstart),0))
print @weekstart
set @weekend=DATEADD(dd,6,@weekstart)
end
select @day= datepart(dw,@EndDate)
if @day=6 or @day=7
begin
insert into #weekdays(weekstart,weekend)
values (@weekstart,@weekend)
end
select CAST(weekstart AS DATE) as WeekStart, CAST(weekend AS DATE) as WeekEnd from #weekdays

This script will gives the following result:-

WeekStart WeekEnd
2015-04-13 2015-04-19
2015-04-20 2015-04-26
2015-04-27 2015-05-03
2015-05-04 2015-05-10
2015-05-11 2015-05-17
2015-05-18 2015-05-24
2015-05-25 2015-05-31
2015-06-01 2015-06-07

Importance of Best Practices in database programming

2015-02-01T11:45:00.000+05:30

For any programming language, just writing the code is not well enough. It should be written using the best practices. This article will try to explain the disadvantages of writing code without using Best Practices and latter on how best practices can be implemented in our database code.

Code written without using best practices has the following disadvantages:-

1) Difficult to maintain:- If the code is not written properly, it will be very difficult to modify the code in future and sometimes it become so messy that we required to rewrite the code again even for a small change.

2) Lot of unusable code left which makes the code unnecessary lengthy: - If we do not do the designing part correctly, we keep changing the code again and again which result in lot of reworking. Due to this most of the time leads to a situation where some procedure is left with function or block of query which is not required but it keep executing.

3) Difficult to understand the code: - One should write a code which can be easily understandable by the other team members. If we do not write the proper comments for each block of code, it becomes difficult to understand the purpose behind the block of code.

4) Poor performance due to improper written complex queries: - In database, there can be multiple ways to write a query which fetch/update/delete records from the tables but performance depends on how we write the queries. If we do not write the optimized queries, it badly affects the performance of the application.

5) Unexpected error or behaviors due to poor exceptional handling:- If exception handling is not done properly, query execution may break in midway and wrong data is inserted into the tables which will corrupt the database.

6) Locking of tables for long duration due to poor transaction handling: - Transactions should be used so that in case of any exception or error, uncommitted data must be rolled back. But sometimes we use transaction so poorly that it locked the main tables for longer time and it resulted in the deadlock situation and long duration of locking period result in poor application performance.

7) Difficult to debug: - If the code is written in a very messy way with lot of unusable codes, it becomes difficult to debug the code and find the error.

Best practices can be divided into 3 sections

1) How much time you spend in design phase of the database

2) How you write the block of code

3) How you write the SQL queries

Time spends on design of the database:-

Best practices says that the more time you spend on the designing phase , the less time you spend on coding, fixing the bugs and rework. It doesn't not says that one should spend all the time in the designing phase itself but one should spend 40%-50% time depending on the project complexity on the design phase. This is true for not only database programming but for any other languages too. In any programming, it has said that we should spend more time on designing rather than in code. For example,

40% of the time was spent on design;

30% of the time was spent on coding;

20% of the time was spent on testing;

10% of the time was spent on documenting.

But most of the developers do exactly the opposite. They spend less time on designing and spend most of the time in coding, reworking on the code and bug fixing. Before start writing any code, one should have done the proper homework on the design and a details technical document should be created whichdescribes how we can divide the requirement into small sections like store procedures, functions etc. One should also match that technical document with the requirement document to make sure that it meets most of the requirement.

Best practices for writing code blocks

According to best practices, we should write code based on the following below parameters:-

1) Maintainability: - The code should be written in such a way that if we need to add further queries or modify the existing queries, we can easily do this. We do not need to rewritten it again and again for changes in the future.

2) Reusable components:-Instead of writing lengthy stored procedures, we should try to divide it into multiple small stored procedures so that if we need, we can use these small stored procedures as reusable code components. It save lots of time by using the same stored procedure again and again.

3) Easy to understand:-Every code of block should have proper comments which should clearly define the purpose of this block of code so that any other team member or any other new person in your team can easily understand the code.

4) Proper error handling:-Code should be written in such a way that no error should go unchecked. In case of error, catch block should give proper error message. SQL has given the try catch block for error handing. Unchecked error sometimes breaks the code in the midway and corrupt data is inserted in the tables. Try…Catch help in maintaining the integrity of the database.

5) Proper transaction handling:-Transactions should be handled properly in the stored procedure so that in case of error, uncommitted data can be easily rollback. Also transaction should not be applied in such a way that it keeps locking the tables for longer duration as it affects the performance of the application. Instead of applying transaction in the whole stored procedures, multiple transactions can be applied in stored procedures by keeping the related block of code in a single transaction.

Best practices for writing SQL Queries:-

1. All tables in the database should have properly defined relationship using primary keys and foreign keys. It helps in maintaining the database integrity and help in increasing the database performance as with each primary key, SQL server automatically created the clustered Indexes.

2. Indexes should be created on the tables as it increases the performance of the SQL Queries. With latest version of SQL Server, we can use Covering Index to include all the columns of the select query in the indexes. But too many indexes can also decreased the performance, as it take cost in maintaining the indexes.

3. Find the indexes which need to be reorganized or rebuild. Indexes are created to increases the performance of the database. But with a period of time, frequent insertion/deletion and updating of data in the tables causes fragmentation which reduces the effect of indexes. We should check fragmentation and accordingly reorganized or rebuild the indexes.

4. Use "set not count on/off" as it will increase the performance of the queries. Unless there is a need to know the number of rows affected by the execution of the query/stored procedure, we should use "set not count on" at the beginning of the code block and "set not count off" at the end of the code block. Calculation of the number of rows affected by the execution of the query/stored procedure add extra cost to the performance.

5. Try to avoid looping using the Set based approach but if looping is necessary, try to avoid the cursor and replaces it with while loop or using switch statements or Sub Queries. Since till the cursor is not deallocated, memory is occupied by the cursor data and in case of large amount of data, it can decrease the performance of the SQL queries.

6. Proper use of temporary tables and table variable. Temporary tables and table variables has its own advantages and disadvantages. If the number of rows which are going to stored in the table is small, then table variable is a good option and if the number of records which is going to be stored in the table is large then we should use the temporary tables as we can create indexes on them which helps in increasing the performance

7. Use Joins instead of Sub queries. We should use joins instead of sub queries as much as possible because in case of sub queries, SQL Server internally tries to use joins between the record set return by the separate execution of queries.

8. Intelligent use of Distinct command. Distinct command adds additional cost to the query. So we should use distinct commands only when we need the unique results.

9. Proper error handling using Try....catch should be done so that execution of the script should not be stop in the midway due to errors and tables data should not get corrupted.

10. Do not use "Select * from tablename" command when you only needs to select few columns from the table. In the select section of the query, we should mention only those columns whose data we required from query as if "select *" will give the data from all the columns of the table and it will decrease the performance of the query.

11. Never use "sp_" prefix in your store procedure name. If we use "sp_" prefix in the procedure name then, SQL will first search the stored procedure within the system procedures and then in the user created stored procedures. So it will result in the waste of time in searching.

13. Use SQL Profiler to monitor the SQL performance. SQL Profiler is the tool provided by the SQL Server. User can use the SQL Profiler to find out the missing indexes or statistics

14. Use stored procedure instead of inline Queries in the code. There are lots of benefits if we use stored procedures which includes re-usability, better Security of Database records due to access rights on stored procedures, good performance due to optimal execution plan maintained by SQL server, easy to maintain and many more...,

15. If we do not need the unique results, use Union all instead of Union.

Summary:-

This article tried to show the disadvantages of not following the best practices and how we can reduce our rework by spending proper time on the designing of the database. Also this article gives put lights on the some best practices which we can use during coding to make our database code more flexible, reusable , easy to understand and more optimised.

RIGHT() function in SQL Server

2015-01-26T19:53:00.000+05:30

Right():- This function returns the specified number of characters from the Right part of the given character string.

Syntax:-

Select RIGHT ( character_expression , integer_expression )

For example, the below query will return two characters from the last.

Select RIGHT('Vivek',2)

Result

ek

Technology with Vivek Johari

Please Vote for my article "SQL Triggers - An Introduction" on CodeProject.

Point In Time Restore for Azure SQL Database

Point In Time Restore for Azure SQL Database

Merry Christmas

SQL Triggers – An Introduction

Migrating SQL Database To Azure SQL Instance using Microsoft Database Migration Assistant (DMA) Tool

Azure Tutorial

Migrating SQL Database to Azure SQL Database using SSMS Export/Import

Contents

Introduction

Migrating SQL Database to Azure SQL Database using SSMS Export data

Creating the .bacpac file using SSMS Export

Importing the .bacpac file using SSMS Import

Summary

Read More

Read more from Tutorials

Blog trasfering to new hosting server

SQL Script to find the database & their files details on a SQL Server

SQL Script: - How to find the database restoring history of SQL Server databases

SQL Script: - How to find the last access date of a database

Understanding Azure SQL Database- Introduction

Introduction:-

Learn How to Recover Deleted Records from SQL Server 2014

How to Get Back Deleted Records in SQL Server: Solution

Recover Deleted Records from SQL Server with LSN

SQL Log Recovery become Hard

Conclusion

Two Ways to Restore Database from MDF File in SQL Server

How to Resume Suspended Database Mirroring in SQL Server ?

Overview

Causes for Database Mirroring Pause

Methods to Fix Database Mirroring Pause?

Steps to Resume the Database Mirroring In SQL Server

Steps to Resume the Database Mirroring Using Transact-SQL

Conclusion

Happy New Year Friends

SQL Recovery Software: An Ultimate Tool For SQL Database Recovery

Outstanding Features of SQL Recovery Software

Happy Independence Day - Jai Hind

Happy Independence Day

SQL Script to find all the triggers defined on a database or on a single table

Interview question - Is Clustered index on column with duplicate values possible?

Question of the Month (July)

Do you know about Whatsapp web?

Script to find complete months between two given dates

Script to find the complete weeks within two given dates

Importance of Best Practices in database programming

RIGHT() function in SQL Server