How to disable constraints of a table ? TIP #74

 

You are reading this post just because of two reason

1) You are curious whether it is possible or not and why we require ?

2) You need to disable constraints  Smile

So , let me share here that you can disable constraints at anytime of a table.

Sometime it is possible when you are doing bulk insert or you need to insert values in column and for this you need to disable constraints.

You can disable all the constraints of a table using following command

ALTER TABLE TABLENAME NOCHECK CONSTRAINT ALL ;

If you want to disable a specific constraint of a table then you can use following syntax

ALTER TABLE TABLENAME NOCHECK CONSTRAINT_NAME

For example suppose you want to disable all the constraints of  student table then you can write following syntax

ALTER TABLE dbo.Students NOCHECK ALL;

Below is very live example in Indian scenario

Suppose you added a check marks in last class more than 45% then only add student now due to some out side pressure you want to give admission to a student who has 40% percent then you need to disable percentage check.

ALTER TABLE dbo.Student NOCHECK chk_Student_Percentage_40

I hope this might help you somewhere.

Thanks

Rj!!!

How to replace a specific string from a large string with Write feature ? TIP #72

 

This is very interesting feature and I recently come to know this awesome feature.

Lets understand this by an example below

Suppose we have a student table with following structure  as shown in below image

structure 

We have a detail column with VARCHAR(MAX) .

Now as it is VARCHAR(MAX) column it may content a large amount of data currently it has following data as shown in below figure

Default_data

Suppose we want to replace a specific string from this large column value like we want to replace “interested in” with “Always”  of  studentId = 1 then

we can use write function easily.

With the help of Write we can update a specific text/string  of a large column.

syntax of Write is as shown below

UPDATE TABLENAME

SET COLUMNNAME.WRITE (ReplacedWithString, startPosition, length)

WHERE clause

Now lets understand this by above student example where we want to replace “Interested in” which is doubled by mistake with “always

writeFunction

So when you run above query you will get following result when you run select command.

result_after_Write

great we have replaced the string which we want.

The main benefit of this the entire column value is not logged. suppose you have 2 GB data in your column then instead of logging 2 GB data at the time update only few KB will be logged.

I hope this might helpful to you somewhere.

Thanks !!!

RJ!!!

How explicitly insert default value ? TIP #72

 

Suppose, sometimes you just need  entry in the table with all the default values.

For example you have a Student table which looks like as below

CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),
                          FirstName VARCHAR(100) DEFAULT ‘RAJAT’,
                          Course    VARCHAR(100) DEFAULT ‘MATHS’)

Now , suppose for in some situation we need just default entry for example in my case I am just trying to insert 100 rows with default value so I need to write below statement

INSERT INTO #tmpStudent (FirstName ,Course ) VALUES (Default, default)

Now when I run the above statement. It insert default value of FirstName,course

I hope this might be helpful to you somewhere.

Enjoy!!!

RJ!!

How to pause execution for particular time or interval ? TIP #71

 

Suppose, We are working on a stored procedure in which some complex operation is going. We are fetching some value from a table and running some custom operations and after ending of this complex operation, we want a pause of suppose 10 seconds to run another job then in such situation we will use

WAITFOR  DELAY HH:MM: SS” 

For example if we want to run select command (second statement) after 10 second delay of first command execution then we will use this as shown in below

INSERT INTO tblStudent (StudentId, StudentName)

VALUES (‘123’,’ABc’)

– in the background we are running some cursor which would take aprox 10 seconds so we are waiting for 10 seconds here

WAITFOR  DELAY 00:00:10

SELECT * FROM tblStudentHistory

In other situation suppose you want to run specific command after particular time then in such cases we will use “WAITFOR TIME  HH: MM: SS”

suppose

“WAITFOR TIME 22:30:45 “   it means SQL statement will pause till 10:30:45 PM

I hope this tips may help you somewhere in your project.

Thanks

Enjoy!!!

RJ!!!

How to copy table structure only from a SQL Query ? Tip #69

 

Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a  SQL Query?”

So,

Below is simplest query to copy structure only of a table into another table.

SELECT *
Into #tmpStudentStructure
FROM tblStudentSource
WHERE 1= 0

in the above query we want to copy structure of tblStudentSource.

see below snap which help you to understand it more

table_Structure

I hope this might help you if some asked you this question.

Thanks & Enjoy!!!

RJ!!!

How easy it is to check which statements consuming most of the CPU & RAM–TIP #68

 

Problem:-  One of the most important question comes in our mind what is the cause of slow  SQL SERVER.

We always struggle with following questions

Which is highly CPU consuming query ?

Which is highly RAM consuming query ?

Who is blocking the transaction ?

and many more other performance dragging questions.

Believe me if you know who is culprit of making your system slow, you will win half battle.

Solution:-  SQL Server provided an easy way to clear your all doubts related to above questions. This easy way is “STANDARD REPORTS”.

You can access Standard Reports option by right clicking the SQL SERVER instance. These Standard reports contains not only performance related reports but other useful reports also.

If you see below image you will find there are many other reports option available.

Top_Cpu_consuming_Query_Indiandotnet

Now, suppose I am interested to know which query consuming high CPU , to achieve this I clicked on   Performance Query – TOP Queries by Average CPU time or Performance query – Top queries  by total CPU time.

When I clicked on any of this option ,I got a report.

This report contained a bar graph & query detail in tabular format which contains query & CPU consuming time as shown below.

Performance_Graph

tabular_Query

in similar way we can get answer of our other performance related query also.

I hope this may help you somewhere.

Thanks & Best Regards,

RJ!!

How to take Transaction Log backup ? TIP# 68

 

As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup .

Before taking transaction backup the first and mandatory condition is that you should have a full backup.

Just right click the database and go to backup option as you will get following screen. Now we need to select Transaction Log   option in   backup type dropdown.

Once this is selected we need to give a transaction log backup name as shown below. The extension should be .TRN

 

TransactionalBackup

 

Once you provided all the mandatory values just click on then you will get your  Transaction Log backup.

We can take Log backup with  SQL command also

For example :-

BACKUP LOG IndiandotnetDB
TO DISK =’E:\IndiandotnetTransaction.trn’

When you run above command you will get following screen and your log backup is done.

Transaction_Log_by__command

I hope this will help you.

Thanks & Enjoy!!!

RJ!!

How to take differential backup ? TIP #67

 

Friends,

We were discussing backup from last few post. Now lets take a look one more feature of backup which is differential backup.

Differential backup  is backup after last full backup. Before taking Differential backup  it is a mandatory condition to take full backup.

Differential backup can speed up your recovery process or we can say restore process.

In below example we would understand how to take differential backup

To take differential backup we can run following command

BACKUP DATABASE IndiandotnetDB
TO DISK = ‘D:\IndiandotnetDB.dif’
WITH DIFFERENTIAL;
GO

Or we can take differential backup by following steps also

Right click the database select database backup option you will get same screen which we explain at the time of full backup.

if you see below screen you will find we have change backup type to Differential and  taking backup to d drive with name Indiandotnetdb.dif

Differential_backup

Once you click OK button. your differential backup will be completed.

I hope this tip might help you somewhere.

Thanks & Enjoy!!!

RJ!!

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66

 

In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN ‘D’ THEN ‘Database’ 
       WHEN ‘L’ THEN ‘Log’ 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail

backup_detail

I hope this tip might help you somewhere.

Thanks !!

Rj!!